Text date as date

[pauldamen]

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

[Kushal_Chawda]

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.

[swuehl]

Have you tried a format like

Date#( YourField, 'DD-MMM-YY') as NewDateField

?

[anagharao]

Hi Paul,

You could use DATE() function in the load script for this.

[MayilVahanan · Accepted solution]

HI

Try like this

Date(Date#( YourField, 'DD-MMM-YY')'DD-MMM-YY') as NewDateField

[pauldamen]

Yep no luck

[swuehl]

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?

[#8]

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.

[swuehl]

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?

[MayilVahanan]

Hi Stefan,

Am converting the text field values to date values with his date format.