I have a date field coming in from excel in this format: 01-Feb-16, which is recognized as a text field instead off date.
I tried all kind op functions like num#, date#, with trims but I can't get it to work.
Set the below variable in script
SET DateFormat ='DD-MMM-YY';
Note : Make sure that Excel Date format should exactly match with SET variable format
Now load the excel file, it will automatically convert to Date.
Is your MonthNames variable set accordingly at the beginning of your script?
Set MonthNames = 'Jan;Feb;Mar,....';
If this doesn't help, could you upload a small sample of your Excel input records?
Try like this
Date(Date#( YourField, 'DD-MMM-YY')'DD-MMM-YY') as NewDateField
Strange, why should adding a formatting function like Date() helps in interpreting the text as date?
As far as I can see, the format code is the same as suggested before.
edit: Or maybe we are actually not coping with pure text values in the input records?