Conversion error calling conversion routine date_from_string
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 8
- Joined: Mon Jan 08, 2007 1:08 am
- Location: Trichy
Conversion error calling conversion routine date_from_string
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
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
Thirumoorthi
-
- Premium Member
- Posts: 145
- Joined: Fri Sep 21, 2007 9:35 am
- Location: Boston
-
- Participant
- Posts: 8
- Joined: Mon Jan 08, 2007 1:08 am
- Location: Trichy
Re: Conversion error calling conversion routine date_from_st
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
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
Thirumoorthi
-
- Participant
- Posts: 8
- Joined: Mon Jan 08, 2007 1:08 am
- Location: Trichy
Re: Conversion error calling conversion routine date_from_st
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
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
Thirumoorthi
-
- Participant
- Posts: 27
- Joined: Thu Mar 20, 2008 11:55 am
Re: Conversion error calling conversion routine date_from_st
Please use %2000yy instead of %yya.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
since the year is 08 it is taking as 1908. So, please mention %2000yy and this will give you the right output 2008.
Thanks.
Thanks.
"Creativity is the ability to use your available resources to their fullest."
"Creativity is the ability to use your available resources to their fullest."
-
- Participant
- Posts: 8
- Joined: Mon Jan 08, 2007 1:08 am
- Location: Trichy
Re: Conversion error calling conversion routine date_from_st
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
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:Please use %2000yy instead of %yya.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
since the year is 08 it is taking as 1908. So, please mention %2000yy and this will give you the right output 2008.
Thanks.
Thirumoorthi
-
- Premium Member
- Posts: 175
- Joined: Tue Oct 14, 2008 1:48 pm
Re: Conversion error calling conversion routine date_from_st
[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
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
Rajesh Kumar
%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?
But I was just curious to know about the workaround Thiru made.
Is padding the rest of YY is the actual workaround?
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'