Current Timestamp in to DB2

cosec · Post by **cosec** » Fri May 18, 2007 1:59 am

I am trying to insert the Current Time Stamp in to a target Column.

when I have the target as a Sequential File My OutPut for that column is of the form :15:07:29 18 MAY 2007

But When My Target is a DB2 Table My Out Put is :

0015-11-27 17:26:43.694000

Any suggestions to correct this ?

JoshGeorge · Post by **JoshGeorge** » Fri May 18, 2007 5:30 am

Easy way is to use is "CURRENT TIMESTAMP" in your insert SQL statement.

chulett · Post by **chulett** » Fri May 18, 2007 6:24 am

The DB2 stage wants dates in internal format, from what I recall.

DSguru2B · Post by **DSguru2B** » Fri May 18, 2007 7:38 am

How are you getting the Current Timestamp? What is the length of your target stage?

cosec · Post by **cosec** » Sun May 20, 2007 7:33 pm

Target Length is 26.

I am using TimeDate() in one of the transformer columns....Do u think I should convert it to internal and then reconvert it ???

chulett wrote:The DB2 stage wants dates in internal format, from what I recall. ...

DSguru2B · Post by **DSguru2B** » Mon May 21, 2007 6:46 am

Well, DB2 accepts date in internal format, I am sure about that. Not too sure about timestamp though.
Try

Code: Select all

OCONV(Date(), "D-YMD[4,2,2]"):" ":OCONV(Time(), "MTS")

rafik2k · Post by **rafik2k** » Mon May 21, 2007 7:24 am

I am using following format to generate current db2 timestamp in my jobs.
you can try

Code: Select all

oconv(@Date,"D-YMD[4,2,2]"):" ":Oconv(@TIME,"MTS")

Result = 2007-05-21 15:21:00

DSguru2B · Post by **DSguru2B** » Mon May 21, 2007 7:25 am

Same thing

rafik2k · Post by **rafik2k** » Mon May 21, 2007 7:28 am

First try to insert one dummy row with timestamp value '2007-05-21 15:21:00' into target db2 table, and see what output you get.

If it's ok then you can try following code.

rafik2k wrote:I am using following format to generate current db2 timestamp in my jobs.
you can try
Code: Select all
oconv(@Date,"D-YMD[4,2,2]"):" ":Oconv(@TIME,"MTS")
Result = 2007-05-21 15:21:00

rafik2k · Post by **rafik2k** » Tue May 22, 2007 2:09 am

DSguru2B wrote:Same thing

DS Guru Ji,
You need zoom lens to check that

DSguru2B · Post by **DSguru2B** » Tue May 22, 2007 7:03 am

Then I will be seeing micro-organisms within the internal dates.

Why I said its the same thing is because both @Date and Date() return internal formats. Same is the case with @Time and Time().