Page 1 of 1
date format
Posted: Fri Sep 08, 2006 10:40 am
by 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.
Posted: Fri Sep 08, 2006 10:48 am
by 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.
Re: date format
Posted: Fri Sep 08, 2006 11:00 am
by 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.
Re: date format
Posted: Fri Sep 08, 2006 11:02 am
by DeepakCorning
I think I messed up with the quoting thing...
Posted: Fri Sep 08, 2006 11:35 am
by 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.
Posted: Fri Sep 08, 2006 11:37 am
by 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,
Posted: Fri Sep 08, 2006 12:05 pm
by 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"
vardhan354 wrote: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,
Posted: Fri Sep 08, 2006 12:35 pm
by DeepakCorning
....To be more safe (As it is a flat file) i will use Z,Z,4 in the iconv function...
Posted: Sun Sep 10, 2006 3:16 am
by 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.
give the oracle varchar format
Posted: Sun Sep 10, 2006 11:24 pm
by 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.