Conversion error calling conversion routine date_from_string

[a.thirumoorthi]

Hi,

I have a file which has five columms. A field "pdate" is char data type which has date values (format ddmmyy eg 011008). This date value is need to be converted before loading into a target table in oracle 9i.
I have used the converion function StringToDate(ln_TTS_DelvyDate_in.pu_date,"%dd-%mm-%yy") to convert the char value into date.
.

The job is: Seq file--->TRANSFORMER---->SeqFile

When I'm executing this job it gives the following error:

Tr_Split,1: Conversion error calling conversion routine date_from_string data may have been lost


In the output file
The "pudate" has '*******' being propagated.


The input date column will not have null values.

Input Data- Char data type

011008

Output Data - date data type

******

Note: I already found some solutions/suggestions from this forum and those were not helped outl.

Any help in resolving this issue would be really appreciated.


Thanks,
Thiru

[Aruna Gutti]

The conversion function should not have hyphens as your data doesn't have it.

StringToDate(ln_TTS_DelvyDate_in.pu_date,"%dd%mm%yy")

should work.

[a.thirumoorthi]

Hi Aruna,

Thanks for your help.

I tried with format "%dd%mm%yy"
StringToDate(ln_TTS_DelvyDate_in.pu_date,"%dd%mm%yy") it is working fine but the problem is the converted date is coming as incorrect data which means incorrect year

Say for example Input data : 011008 (01-10-2008)
After conversion : 01101908

So i had done a work around and closed the issue.

Once again Thank you very much Aruna



Thanks,
Thiru

[a.thirumoorthi]

Hi Aruna,

Thanks for your help.

I tried with format "%dd%mm%yy"
StringToDate(ln_TTS_DelvyDate_in.pu_date,"%dd%mm%yy") it is working fine but the problem is the converted date is coming as incorrect data which means incorrect year

Say for example Input data : 011008 (01-10-2008)
After conversion : 01101908

So i had done a work around and closed the issue.

Once again Thank you very much Aruna



Thanks,
Thiru

[filename.txt]

converted date is coming as incorrect data which means incorrect year

Say for example Input data : 011008 (01-10-2008)
After conversion : 01101908

Please use %2000yy instead of %yy

since the year is 08 it is taking as 1908. So, please mention %2000yy and this will give you the right output 2008.


Thanks.

[a.thirumoorthi]

Hi

Thanks for ur information

I don't think it would give correct data for the year 2100

Please advice me, what will happen if year is 2100

Thanks
Thiru

converted date is coming as incorrect data which means incorrect year

Say for example Input data : 011008 (01-10-2008)
After conversion : 01101908

Please use %2000yy instead of %yy

since the year is 08 it is taking as 1908. So, please mention %2000yy and this will give you the right output 2008.


Thanks.

[kumar_s]

Hi Thiru,
May I know what is the work around that you are using currently?

[kittu.raja]

[quote="a.thirumoorthi"]Hi,

I have a file which has five columms. A field "pdate" is char data type which has date values (format ddmmyy eg 011008). This date value is need to be converted before loading into a target table in oracle 9i.
I have used the converion function StringToDate(ln_TTS_DelvyDate_in.pu_date,"%dd-%mm-%yy") to convert the char value into date.
.
Hi Thiru,

The format of date in datastage is dd-mm-yyyy and you cant change it to just yy. thats y it tells you truncating the data. Try padding the remaining two places with zeroes, then it works.

Thanks

[kumar_s]

%2000yy will work for 21st century. Its way too much ahead to think about 22nd second century anyway.
But I was just curious to know about the workaround Thiru made.

Is padding the rest of YY is the actual workaround?