Date Conversion

[Hope]

I have to load Date(Flat file as source and datatype as Timestamp (23,3))
ModifiedDate = "04/02/2010 01:00:05 AM" in to
Target table (SQL table as) "2010-04-06 23:50:46.000"

Can anyone please help me with the conversion


Thanks
Hope

[ETLJOB]

If I understand correctly, your source date is "02/APR/2010" and the target date is "06/APR/2010". I don't think this is just a data type conversion. Do you have any specific logic that needs to be imposed in this conversion?

[Hope]

Date formates are as below

Source Date =04/02/2010 01:00:05 AM and I have to do some conversion and Target Date should be 2010-04-02 01:00:05.000

[chulett]

Split the date from the time, use the IConv/OConv functions to convert each component and then cat them back together. If you really need the milliseconds then just stick the ".000" on the end.

Both 'conversion' functions are well documented in the Help, give that a shot then let us know what you've tried and what issues (if any) you have with the approach.

[Hope]

I did split the date and timestamp and wrote a conversion for the date .

OConv(Iconv(DSLink4.ModifiedDate[1,10],"D/DMY[2,2,4]"),"D-Y-M-D")

The out put of this function is 15346.Its not giving me the target date as 2010-04-02 .
Is my logic right?Please correct me if I am wrong.

[chulett]

Pretty close.

The 'output' conversion mask is off and needs to be more like what you have on the input side:

Code: Select all

OConv(Iconv(DSLink4.ModifiedDate[1,10],"D/DMY[2,2,4]"),"D-YMD[4,2,2]")

Technically, the IConv mask can be simplified but that should work more better for you.

[anbu]

Code: Select all

OConv(Iconv(DSLink4.ModifiedDate[1,10],"D/DMY[2,2,4]"),"D-YMD[4,2,2]")

[Hope]

Thank you,

The logic works

[asorrell]

Shouldn't it have been "more betterer" Craig? Or the more informal "Mo' Bettah" that we use down South?

[chulett]