Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
All,
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.
Any suggestions?
Regards, Paul
HI
Try like this
Date(Date#( YourField, 'DD-MMM-YY')'DD-MMM-YY') as NewDateField
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.
Have you tried a format like
Date#( YourField, 'DD-MMM-YY') as NewDateField
?
Hi Paul,
You could use DATE() function in the load script for this.
HI
Try like this
Date(Date#( YourField, 'DD-MMM-YY')'DD-MMM-YY') as NewDateField
Yep no luck
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 this :
Date(Date#( YourField, 'DD-MMM-YY') , 'DD-MMM-YY')as NewDateField
Date# will convert your date to text format and date will convert it back to date format.
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?
Hi Stefan,
Am converting the text field values to date values with his date format.