Page 1 of 2
DB2 Timestamp Format
Posted: Tue May 18, 2004 11:55 am
by JDionne
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
Posted: Tue May 18, 2004 1:42 pm
by vinnz
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: Select all
TIMESTAMP ('2004-05-18-12.00.00.000000')
TIMESTAMP ('2004-05-18 12:00:00')
[/code]
Posted: Tue May 18, 2004 1:44 pm
by ogmios
Are you using an ODBC stage or a DB2 stage to connect to your target?
Is you're source an ODBC stage or a DB2 stage?
Ogmios
Posted: Tue May 18, 2004 1:53 pm
by JDionne
ogmios wrote:Are you using an ODBC stage or a DB2 stage to connect to your target?
Is you're source an ODBC stage or a DB2 stage?
Ogmios
Im using the db2 stage and my source is a sequencial file
Jim
Posted: Tue May 18, 2004 2:14 pm
by ogmios
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.
Posted: Tue May 18, 2004 2:31 pm
by JDionne
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
Posted: Wed May 19, 2004 10:13 am
by JDionne
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
Posted: Wed May 19, 2004 11:12 am
by ogmios
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:
Code: Select all
"2004-05-24" : " " : Oconv("00:00:00","MTHS")
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):
Code: Select all
In.Date : " " : Oconv("00:00:00","MTHS")
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
Posted: Wed May 19, 2004 11:53 am
by JDionne
AGGGG
I used this line:
Code: Select all
"2004-05-24" : " " : Oconv("00:00:00","MTHS")
and it worked, so that means there is a problem with my oconv inconv.
this is my code:
Code: Select all
OCONV(ICONV(Exp_Stg_Load.VDATE, "D-YMD[4,2,2,]"), "D4-MDY[2,2,4]")
so whats up with this?
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:
Code: Select all
"2004-05-24" : " " : Oconv("00:00:00","MTHS")
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):
Code: Select all
In.Date : " " : Oconv("00:00:00","MTHS")
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
Posted: Wed May 19, 2004 12:00 pm
by ogmios
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
Posted: Wed May 19, 2004 12:09 pm
by JDionne
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
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
Posted: Wed May 19, 2004 12:18 pm
by ogmios
The "am" is not good in time, DB2 is very picky on its input. Try "MTS" for the time part.
Ogmios
Posted: Wed May 19, 2004 12:26 pm
by JDionne
ogmios wrote:The "am" is not good in time, DB2 is very picky on its input. Try "MTS" for the time part.
Ogmios
out put of code:
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
Posted: Wed May 19, 2004 12:29 pm
by ogmios
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
Posted: Wed May 19, 2004 12:49 pm
by JDionne
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
ohh the world is complete!!!! the bit of knowlage that i needed, the DB2 Date Format!!!!!!!!
you are a god man
thanx
Jim