can't read date column from excel sheet

[h4harry1]

Hi All,

I have an excel sheet, in this sheet i have a date column, i am trying to fetch the data from this sheet and storing this is db2 database. I have a problem in this , some of the dates in excel sheet are:-
1. written as 0
2. written in the format as mm/dd/yyyy.
3. written in the format m/dd/yyyy
4. written in the format as mm/d/yyyy.

In the transformer stage i have given the following condition for date column:-

if DSLink36.END_DATE_01='0' then SetNull() else StringToDate(DSLink36.END_DATE_01,"dd/%mm/%yyyy")

This works fine if the date is 0 or in the format of mm/dd/yyyy.

But it gives run time error when trying to process the date which is in the format of m/dd/yyyy, mm/d/yyyy etc. Here i understand the problem , which is it expects 2 digits for day, 2 digits for month and four digits for year , but don't know how to solve it.

any help is appreciated.

Thanks.

[chulett]

You need to ensure that each 'segment' of the date is the correct number of digits as you've found. That means breaking it up, padding the ones that need a leading zero, putting it back together and then calling the function. That or enforce a Date format in the spreadsheet that lets you not need to do that.

[ds@tcs]

Use transformer stage, in the transformer use day from date and month from date , then which rows r having single char date or month for that rows add 0, then gain conver to date , try this..

[jwiles]

How about a format string of "%m/%d/%yyyy"? %m and %d will handle either 1 or 2 digit values, as is indicated in the product documentation "Parallel Job Developer Guide".

Regards,

[chulett]

Hmmm... based on the advice I've seen given out over the years, I'm thinking the documentation may be incorrect. That or the advice.

[ray.wurlod]

There are variants available, like "%(m,v)/%(d,v)/%yyyy", at least in version 8.5.