date conversion error

[kavuri]

Hi,
I have a job which is reading date as varchar(10). I am trying to convert this into date field by using the following command.

StringToDate(inColumn,"%mm/%dd/%yyyy")

My input data is in the folowing format
1/1/2010

here below is my error.
Fatal Error: Invalid Julian day [date/date.C:1006]

I took the job seperate and tried to read this field alone. Then I am not getting fatal error, but losing the value and following warning is read.
Conversion error calling conversion routine date_from_ustring data may have been lost [transform/tfmop_functions.C:132]

Any help is greatly appreciated.

Thanks
Kavuri

[kavuri]

Hi,
I had used the work around logic to solve this. If you have any better logic or simple logic to do this let me know.

month1=lnk_odbc_SQLServer.CREATE_DATE['/',1,1]
day1=lnk_odbc_SQLServer.CREATE_DATE['/',2,1]
year1=lnk_odbc_SQLServer.CREATE_DATE['/',3,1]
month=If Len(month1) = 1 then 0:month1 else month1
day=If Len(day1) = 1 then 0:day1 else day1
year=year1
date=month:day:year

target_date=StringToDate(date,"%mm%dd%yyyy")

Thanks
Bhargava

[chulett]

You are on the right track with padding the single digit months/days with a leading zero so it matches the format mask the conversion function needs. You can simplify what you are doing (using syntax that I don't recall off the top of my head but which has been posted here numerous times) but it still needs to be done.

[anbu]

Code: Select all

month=Right('00':month1,2)
day=Right('00':day1,2)

[chulett]

Thanks... and unless it gets more complicated, you can do all that inside the StringToDate function rather than in several stage variables.

[kavuri]

Thanks to both of you.