DSXchange

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,

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.

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

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,

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

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....

Just do some tweaking with the code:

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....

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.

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 .

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

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

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

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

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.....

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.