date format

[vardhan354]

Hi,

i have source sequential file Date (varchar),

Target oracle database Date(Timestamp).

in transformer i am using the oconv like this

oconv(iconv(DSLINK2.COLUMN NAME, "DYMD"), "D-YMD[4,2,2]")

please correct me from wrong.

Thanks for your help.

[kris007]

Firstly, are you getting any error messages?

Does your input contain timepart or is it only date. If so, and if your target datatype is Timestamp then you will have to append 00:00:00 at the end of your derivation and that will work.

[DeepakCorning]

[quote="vardhan354"]
i have source sequential file Date (varchar),
quote]

What is the format in the sequential file for the date field , for e.g. is it MMDDYYYY or DDMMYYYY as your usage of ICONV will depend on what format you receiveing.

[DeepakCorning]

I think I messed up with the quoting thing...

[meena]

Hi
As datatype is a timestamp you need to concatenate the date with time.

Code: Select all

oconv(iconv(DSLINK2.COLUMN NAME, "DYMD"), "D-YMD[4,2,2]") : "00:00:00"

Target oracle database Date(Timestamp).

in transformer i am using the oconv like this

oconv(iconv(DSLINK2.COLUMN NAME, "DYMD"), "D-YMD[4,2,2]")

please correct me from wrong.

[vardhan354]

thanks for youre response meena, Deepak.


The source date format is dd-mm-yyyy,

there is no timestamp in source.

Basing on this information is this correct

oconv(iconv(DSLINK2.COLUMN NAME, "DYMD"), "D-YMD[4,2,2]")


Thanks,

[meena]

Hi,
I am not talking about the source datatype. I am talking about the target datatype.You mentioned it as Timestamp.Based on the input, your conversion is wrong.Check at Iconv funtion. Check this

Code: Select all

oconv(iconv(DSLINK2.COLUMN NAME, "D-DMY[2,2,4]"), "D-YMD[4,2,2]"):"00:00:00" 

thanks for youre response meena, Deepak.


The source date format is dd-mm-yyyy,

there is no timestamp in source.

Basing on this information is this correct

oconv(iconv(DSLINK2.COLUMN NAME, "DYMD"), "D-YMD[4,2,2]")


Thanks,

[DeepakCorning]

....To be more safe (As it is a flat file) i will use Z,Z,4 in the iconv function...

[ray.wurlod]

It doesn't matter. Iconv() is clever enough to work it out. All you must provide is the first "D" and the date component designators (for example "MDY").
This article may help to explain why this is so.

[changming]

if your source date format is varchar dd-mm-yyyy,
just use substring function to convert it to yyyymmdd.
Orcale will work very well on this string.