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
SQL dates and DS
Moderators: chulett, rschirm, roy
SQL dates and DS
Sure I need help....But who dosent?
Re: SQL dates and DS
Which kind of database stage generates the error? There are huge differences between the different kind of stages.
Ogmios
Ogmios
Last edited by ogmios on Thu Jan 29, 2004 2:06 pm, edited 1 time in total.
Re: SQL dates and DS
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 -.
oconv(iconv(Input.Update_Date, "D-ymd[4,2,2]"), "D4-YMD[4,2,2]") : Oconv("00:00:00","MTS")
replacing the / with -.
Re: SQL dates and DS
ogmios wrote: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
Sure I need help....But who dosent?
Re: SQL dates and DS
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:
Convert the date using '/'s to internal format, and then changing to a format compatible with ODBC (using -'s).
Ogmios
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"
Ogmios
Re: SQL dates and DS
justlrng wrote: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
Sure I need help....But who dosent?