Illegal date/time value warning

[datskosaraju]

Hi all,

I am trying to insert some data into table(mysql database) using odbc stage.

Code: Select all

  sequential stage ------->TX--------->ODBC

One of the columns in the table is nullable with date as a datatype. The input for the date is a char in the input file. I am using oconv to convert that to date and assigning a default NULL for null values. The inserts for non nullable values from the input are going on fine, but for null values I am getting the below error

SQL statement:INSERT INTO table (USER_ID, MIDDLENAME, FIRSTNAME, LASTNAME, ZIPCODE, DATE, TITLE, GENDER, MIGRATION_TIMESTAMP, UPDATED) VALUES (?,?,?,?,?,?,?,?,?,?)
SQLSTATE=22008, DBMS.CODE=0
[DataStage][SQL Client]Illegal date/time value

USER_ID = 12345
MIDDLENAME = ""
FIRSTNAME = "XXXXX"
LASTNAME = "XXX"
ZIPCODE = "123456"
DATE = NULL
TITLE = ""
GENDER = "UNKNOWN"
MIGRATION_TIMESTAMP = "2010-09-23 19:49:13"
UPDATED = "2010-09-23 19:49:13"

FYI :I am able to execute the same query with the same values with success using sql tool.

Did anyone face this issue?
I am posting this only after searching the dsxchange.com extensively.

[kris007]

What function are you using to set the NULL value?

[ray.wurlod]

Is the DATE column nullable in the target?

[datskosaraju]

@kris I am just hard coding the value 'NULL'

@Ray - Yes the date is Nullable in the database.

[datskosaraju]

I found a workaround for this.

Instead of using OConv to convert the incoming string to date and passing it on to the target, I am just passing on the without converting and doing the conversion before the inserting in to the target( the user defined sql (str_to_date("?", "%m%d%Y")).


will wait for the responses and mark it as resolved since this is just a workaround :D