DateTimestamp
Moderators: chulett, rschirm, roy
DateTimestamp
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,
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,
RK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
In a server job just format the concatenation.
Not sure how you established that 06 is pm in your example, but ISO 8601 specifies 24 hour clock for a timestamp.
Code: Select all
Fmt(col1:col2,"####-##-## ##:##:##")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
hi Ray,ray.wurlod wrote:In a server job just format the concatenation.Not sure how you established that 06 is pm in your example, but ISO 8 ...Code: Select all
Fmt(col1:col2,"####-##-## ##:##:##")
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,
RK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi Ray,ray.wurlod wrote:Yes, put the"-" characters into the Fmt mask. ...
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....
RK
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
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 haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
Thomas Alva Edison(1847-1931)
I tried you code it didnot work am getting the out put like thisus1aslam1us 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,"##/##/#### ##:##:##")
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....
RK
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
Just try this piece:
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.
Code: Select all
Oconv(Iconv(InDWWORK.DW_ADJ_DATE,"DYMD[4,2,2]"),"D/MDY[2,2,4]"):" ":InDWWORK.DW_ADJ_TIME
I haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
Thomas Alva Edison(1847-1931)
It just concats the string with that also it is not the format of timestamp where am getting like 07/16/2007 043146 .us1aslam1us wrote:Just try this piece:
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.Code: Select all
Oconv(Iconv(InDWWORK.DW_ADJ_DATE,"DYMD[4,2,2]"),"D/MDY[2,2,4]"):" ":InDWWORK.DW_ADJ_TIME
RK
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Here is what i wantray.wurlod wrote:Please specify EXACTLY what you want to get out, including its data type. ...
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.....
RK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Reorder the date components, and format into a timestamp. Simple string manipulation.
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.
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")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.