Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pauldamen
Partner - Creator II
Partner - Creator II

Text date as date

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

1 Solution

Accepted Solutions
MayilVahanan

HI

Try like this

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

10 Replies
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
MVP
MVP

Have you tried a format like

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

?

anagharao
Creator II
Creator II

Hi Paul,

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

MayilVahanan

HI

Try like this

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
pauldamen
Partner - Creator II
Partner - Creator II
Author

Yep no luck

swuehl
MVP
MVP

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?

Not applicable

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

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.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.