DB2 Timestamp Format
Moderators: chulett, rschirm, roy
DB2 Timestamp Format
I am haveing the worst time formating a date to go into my DB2 UDB server. This is a date out of the server: Aug 21, 2003 2:13:44 PM
This is what Im outputing in DS: Mar 01 2004 00:00:00
using the dirivation OCONV(ICONV(Exp_Stg_Load.VDATE, "D-YMD[4,2,2,]"), "DMDY[A3,2,4,]") :" " : Oconv("00:00:00","MTHS")
and im getting the error: EXP1LoadStgTbl..Transformer_115: [IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
where am I off in my format?
Jim
This is what Im outputing in DS: Mar 01 2004 00:00:00
using the dirivation OCONV(ICONV(Exp_Stg_Load.VDATE, "D-YMD[4,2,2,]"), "DMDY[A3,2,4,]") :" " : Oconv("00:00:00","MTHS")
and im getting the error: EXP1LoadStgTbl..Transformer_115: [IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
where am I off in my format?
Jim
Sure I need help....But who dosent?
DB2 stores timestamps with microseconds ie 2000-07-31-10.30.15.610208
You can try inserting by formatting it into YYYY-MM-DD-hh.mm.ss.zzzzzz
If its user defined SQL then you can also use the DB2 timestamp functions.
To convert a character string to a date or time value, you can use:[/code]
You can try inserting by formatting it into YYYY-MM-DD-hh.mm.ss.zzzzzz
If its user defined SQL then you can also use the DB2 timestamp functions.
To convert a character string to a date or time value, you can use:
Code: Select all
TIMESTAMP ('2004-05-18-12.00.00.000000')
TIMESTAMP ('2004-05-18 12:00:00')
The DB2 stage as a target wants to receive the "internal format" for dates. For only a date I use the following in a transformer:
when the input is something as 2004-05-18
I don't find an example in my jobs for a timestamp conversion, but using the written months (MAR instead of 3) has never worked for me.
Ogmios
P.S. Just remebered something, I think you can get away with just formatting your field in the right format for timestamps as in the mail of vinnz.
Code: Select all
Iconv(In.Date, "D4-YMD[4,2,2]")
I don't find an example in my jobs for a timestamp conversion, but using the written months (MAR instead of 3) has never worked for me.
Ogmios
P.S. Just remebered something, I think you can get away with just formatting your field in the right format for timestamps as in the mail of vinnz.
AGGGGGGGG
not haveing a good day. So what Im doing is wrong obviously. Can any one tell me what formate DB2 wants to see a timestamp in so that I can formate it in that way. I have looked at the data and what I see is aparently something that DB2 displays but not what it actualy stores. Any help would be great!!
Jim
not haveing a good day. So what Im doing is wrong obviously. Can any one tell me what formate DB2 wants to see a timestamp in so that I can formate it in that way. I have looked at the data and what I see is aparently something that DB2 displays but not what it actualy stores. Any help would be great!!
Jim
Sure I need help....But who dosent?
Ok here is the update. I am running the following code:
OCONV(ICONV(Exp_Stg_Load.VDATE, "D-YMD[4,2,2,]"), "D4-MDY[2,2,4]") : " " : oconv(00:00:00, "MTS")
getting the following error from the Director: EXP1LoadStgTbl..Transformer_115: [IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
any ideas?
Jim
OCONV(ICONV(Exp_Stg_Load.VDATE, "D-YMD[4,2,2,]"), "D4-MDY[2,2,4]") : " " : oconv(00:00:00, "MTS")
getting the following error from the Director: EXP1LoadStgTbl..Transformer_115: [IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
any ideas?
Jim
Sure I need help....But who dosent?
I had some time to make a test job.
As a first put the following in your transformer that links to the timestamp in DB2:
So you in fact you actually hardcode the timestamp. Now run the job, if it works do the following, else write back with what you get as output.
I'm assuming your input column is of the format YYYY-MM-DD (if it's not you can easily replace that part):
Where In.Date is your column (as varchar e.g.). If this works you just have to make sure you get the output for the date part in the right format as a string (by using Iconv/Oconv combos).
Ogmios
As a first put the following in your transformer that links to the timestamp in DB2:
Code: Select all
"2004-05-24" : " " : Oconv("00:00:00","MTHS")
I'm assuming your input column is of the format YYYY-MM-DD (if it's not you can easily replace that part):
Code: Select all
In.Date : " " : Oconv("00:00:00","MTHS")
Ogmios
AGGGG
I used this line:
and it worked, so that means there is a problem with my oconv inconv.
this is my code:
so whats up with this?
Jim
I used this line:
Code: Select all
"2004-05-24" : " " : Oconv("00:00:00","MTHS")
this is my code:
Code: Select all
OCONV(ICONV(Exp_Stg_Load.VDATE, "D-YMD[4,2,2,]"), "D4-MDY[2,2,4]")
Jim
ogmios wrote:I had some time to make a test job.
As a first put the following in your transformer that links to the timestamp in DB2:
So you in fact you actually hardcode the timestamp. Now run the job, if it works do the following, else write back with what you get as output.Code: Select all
"2004-05-24" : " " : Oconv("00:00:00","MTHS")
I'm assuming your input column is of the format YYYY-MM-DD (if it's not you can easily replace that part):
Where In.Date is your column (as varchar e.g.). If this works you just have to make sure you get the output for the date part in the right format as a string (by using Iconv/Oconv combos).Code: Select all
In.Date : " " : Oconv("00:00:00","MTHS")
Ogmios
Sure I need help....But who dosent?
Removeing the coma and sending the out put to a flat file gets me the output of: 03-01-2004 12:00:00am
using the following code: OCONV(ICONV(Exp_Stg_Load.VDATE, "D-YMD[4,2,2]"), "D-MDY[2,2,4]") : " " : oconv(00:00:00, "MTHS")
PS. removing the comma did not alow for a sucessfull load.
Jim
using the following code: OCONV(ICONV(Exp_Stg_Load.VDATE, "D-YMD[4,2,2]"), "D-MDY[2,2,4]") : " " : oconv(00:00:00, "MTHS")
PS. removing the comma did not alow for a sucessfull load.
Jim
ogmios wrote:The extra comma e.g. in "D-YMD[4,2,2,]" at the end.
Reroute the output of your transformer to a sequential file and look in the sequential file what you find there (take the same column types as in the database)
Ogmios
Sure I need help....But who dosent?
out put of code:ogmios wrote:The "am" is not good in time, DB2 is very picky on its input. Try "MTS" for the time part.
Ogmios
03-01-2004 00:00:00
code: OCONV(ICONV(Exp_Stg_Load.VDATE, "D-YMD[4,2,2]"), "D-MDY[2,2,4]") : " " : oconv(00:00:00, "MTS")
Still failed
Jim
Sure I need help....But who dosent?
ohh the world is complete!!!! the bit of knowlage that i needed, the DB2 Date Format!!!!!!!!ogmios wrote:The date part needs to be YYYY-MM-DD unless your DBA change local settings. I'm getting "foggy" after programming datastage jobs for 8 hours.
Ogmios
you are a god man
thanx
Jim
Sure I need help....But who dosent?