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.
can't read date column from excel sheet
Moderators: chulett, rschirm, roy
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: