SQL dates and DS

[JDionne]

I have a sequenctial file that has a date field in it. In my transform I use the following code
oconv(iconv(Input.Update_Date, "D-ymd[4,2,2]"), "D/MDY[2,2,4]")

this outputs correctly. I am tring ot load to a colum of the data type DateTime. I get the following error
JOCDEVLoadToServer..Into_SQL_Server: At row 30, link "OutPut", while processing column "Update_Date"
Value treated as NULL
Attempt to convert String value "01/29/2004" to Timestamp type unsuccessful

if i use the code oconv(iconv(Input.Update_Date, "D-ymd[4,2,2]"), "D/MDY[2,2,4]") : " 00:00:00" I get the error

JOCDEVLoadToServer..Into_SQL_Server: OLE DB provider error: The statement has been terminated.

What am I doing wrong?

Jim

PS I am trying to go from varchar in the flat file to a column defined as timestamp in DS wich is actualy datetime in SQL

[ogmios]

Which kind of database stage generates the error? There are huge differences between the different kind of stages.

Ogmios

[justlrng]

Try reformatting so that your statement reads

oconv(iconv(Input.Update_Date, "D-ymd[4,2,2]"), "D4-YMD[4,2,2]") : Oconv("00:00:00","MTS")

replacing the / with -.

[JDionne]

Which kind of database stage generates the error? There are huge differences between the different kind of stages.

Ogmios

Not sure what u are asking but I have three stages in the job a Sequencial a transformer and an ODBC. Im guessing that the error is originating at teh ODBC stage
Jim

[ogmios]

Some stages as the DB2 stage want input/output in 'internal format" for dates, while ODBC stages use "string" input/output.

So not sure as I don't have datastage access right now but I would switch the "format"s as:

Code: Select all

oconv(iconv(Input.Update_Date,  "D/MDY[2,2,4]"), "D-ymd[4,2,2]") : " 00:00:00" 

Convert the date using '/'s to internal format, and then changing to a format compatible with ODBC (using -'s).

Ogmios

[JDionne]

Try reformatting so that your statement reads

oconv(iconv(Input.Update_Date, "D-ymd[4,2,2]"), "D4-YMD[4,2,2]") : Oconv("00:00:00","MTS")

replacing the / with -.

This seems to have fixed it...
oconv(iconv(Input.Update_Date, "D-ymd[4,2,2]"), "D/MDY[2,2,4]") : " " : Oconv("00:00:00","MTS")

I can only guess that the data type in SQL didnt like the seconds in a text string. we will find out later this month when I have another run.
Thanx All
Jim