DSXchange

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.

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.

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..

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,

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.

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