DateTimestamp

g_rkrish · Post by **g_rkrish** » Mon Jul 23, 2007 11:04 am

hi,

I have job in which i have two columns,one has the date as string and another one has the time stamp alone as string..Is any way can i convert that to date time stamp..For ex

column1:20070809
Column2:061000

ReqdOutput:08/09/2007 06:10:00pm

I tried using the iconv ocnv..also i tried oncat the string and use MTHS option on iconv..the o/p comes empty....

Thanks,

ray.wurlod · Post by **ray.wurlod** » Mon Jul 23, 2007 1:58 pm

In a server job just format the concatenation.

Code: Select all

Fmt(col1:col2,"####-##-## ##:##:##")

Not sure how you established that 06 is pm in your example, but ISO 8601 specifies 24 hour clock for a timestamp.

jguerrero · Post by **jguerrero** » Mon Jul 23, 2007 4:55 pm

Try this in a routine

D=oconv(iconv(Arg1,"DYMD[4,2,2]"),"D/YMD[4,2,2]")
if Arg2<120000 then
S="AM"
end
else
S="PM"
end
T=Arg2[1,2]:":":Arg2[3,2]:":":Arg2[5,2]
Ans=D:" ":T:S

g_rkrish · Post by **g_rkrish** » Tue Jul 24, 2007 9:52 am

ray.wurlod wrote:In a server job just format the concatenation.
Code: Select all
Fmt(col1:col2,"####-##-## ##:##:##")
Not sure how you established that 06 is pm in your example, but ISO 8 ...

hi Ray,

I used the code given by you but modified little bit my code is like this...

Fmt(Oconv(Iconv(InDWWORK.DW_ADJ_DATE,"DYMD[4,2,2]"),"D/MDY[2,2,4]"): InDWWORK.DW_ADJ_TIME,"########## ##:##:##")

but when i try to insert into my target databse DB2 it is poping out beacuse it is not time stamp data type...I snay way to convert that to time stamp..I think the Dstranform time stamp is for data to timestamp....

thanks,

ray.wurlod · Post by **ray.wurlod** » Tue Jul 24, 2007 2:07 pm

Yes, put the"-" characters into the Fmt mask.

g_rkrish · Post by **g_rkrish** » Tue Jul 24, 2007 2:54 pm

ray.wurlod wrote:Yes, put the"-" characters into the Fmt mask. ...

Hi Ray,

I f i give that format am not getting the reqd format where it comes out like 08-/0-7/20-/.....Also i changed the fromat to the one you given it gives the eror in assignment error..what we can do about...Pls help me out....

us1aslam1us · Post by **us1aslam1us** » Tue Jul 24, 2007 3:15 pm

Just do some tweaking with the code:

Code: Select all

Fmt(Oconv(Iconv(InDWWORK.DW_ADJ_DATE,"DYMD[4,2,2]"),"DMDY[2,2,4]"): InDWWORK.DW_ADJ_TIME,"##/##/#### ##:##:##")

g_rkrish · Post by **g_rkrish** » Tue Jul 24, 2007 4:18 pm

us1aslam1us wrote:Just do some tweaking with the code:
Code: Select all
Fmt(Oconv(Iconv(InDWWORK.DW_ADJ_DATE,"DYMD[4,2,2]"),"DMDY[2,2,4]"): InDWWORK.DW_ADJ_TIME,"##/##/#### ##:##:##") 

I tried you code it didnot work am getting the out put like this

07//1/6/20 07:04:31 so what i did was i changed to
Fmt(Oconv(Iconv(InDWWORK.DW_ADJ_DATE,"DYMD[4,2,2]"),"DMDY[2,2,4]"): InDWWORK.DW_ADJ_TIME,"########## ##:##:##")

The out put comes right but the dta type comes as string so when i try to insert am getting the error saying string value assgined to the datetime data type....

us1aslam1us · Post by **us1aslam1us** » Tue Jul 24, 2007 4:48 pm

Just try this piece:

Code: Select all

Oconv(Iconv(InDWWORK.DW_ADJ_DATE,"DYMD[4,2,2]"),"D/MDY[2,2,4]"):" ":InDWWORK.DW_ADJ_TIME

Check for the timestamp format at the output, if needed append the milliseconds. Two other things which need to be considered is Null- Handling and Input date format.

g_rkrish · Post by **g_rkrish** » Tue Jul 24, 2007 5:04 pm

us1aslam1us wrote:Just try this piece:
Code: Select all
Oconv(Iconv(InDWWORK.DW_ADJ_DATE,"DYMD[4,2,2]"),"D/MDY[2,2,4]"):" ":InDWWORK.DW_ADJ_TIME
Check for the timestamp format at the output, if needed append the milliseconds. Two other things which need to be considered is Null- Handling and Input date format.

It just concats the string with that also it is not the format of timestamp where am getting like 07/16/2007 043146 .

us1aslam1us · Post by **us1aslam1us** » Wed Jul 25, 2007 9:58 am

Sorry its my bad. You can substring the time part and concatenate it.

g_rkrish · Post by **g_rkrish** » Wed Jul 25, 2007 11:00 am

us1aslam1us wrote:Sorry its my bad. You can substring the time part and concatenate it.

will the data type will be Timestamp?......

ray.wurlod · Post by **ray.wurlod** » Wed Jul 25, 2007 1:11 pm

Please specify EXACTLY what you want to get out, including its data type.

g_rkrish · Post by **g_rkrish** » Wed Jul 25, 2007 2:03 pm

ray.wurlod wrote:Please specify EXACTLY what you want to get out, including its data type. ...

Here is what i want

column1:20070809 Datatype:Decimal (8) which is CFF source
Column2:061000 Datatype:char(6) ,, ,, ,,,,,,,

ReqdOutput:08/09/2007 06:10:00 am Datatype Timestamp (DB2 database)

Pls let me know if you need any more info.....

ray.wurlod · Post by **ray.wurlod** » Wed Jul 25, 2007 11:35 pm

Reorder the date components, and format into a timestamp. Simple string manipulation.

Code: Select all

Col1[5,2] : "/" : Col1[7,2] : "/" : Col1[1,4] : " " : Col2[1,2] : ":" : Col2[3,2] : ":" : Col2[5,2] : " " : (If Col2[1,2] < 12 Then "am" Else "pm")

Still need to learn how you handle times earlier than 1:00 am - you may need to tweak the formula slightly, as well as providing a means to differentiate 12:15am from 12:15pm in your source data.