Page 1 of 1
Conversion error calling conversion routine date_from_string
Posted: Thu Oct 09, 2008 9:09 am
by 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
Posted: Thu Oct 09, 2008 1:22 pm
by 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.
Re: Conversion error calling conversion routine date_from_st
Posted: Mon Oct 13, 2008 10:20 am
by 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
Re: Conversion error calling conversion routine date_from_st
Posted: Mon Oct 13, 2008 10:55 am
by 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
Re: Conversion error calling conversion routine date_from_st
Posted: Mon Oct 13, 2008 1:11 pm
by filename.txt
a.thirumoorthi wrote: 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.
Re: Conversion error calling conversion routine date_from_st
Posted: Wed Oct 15, 2008 4:47 am
by 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
filename.txt wrote:a.thirumoorthi wrote: 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.
Posted: Wed Oct 29, 2008 10:47 pm
by kumar_s
Hi Thiru,
May I know what is the work around that you are using currently?
Re: Conversion error calling conversion routine date_from_st
Posted: Thu Oct 30, 2008 10:29 am
by 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
Posted: Thu Oct 30, 2008 10:35 am
by 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?