Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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

Re: Text date as date

HI

Try like this

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

View solution in original post

10 Replies
Highlighted

Re: Text date as date

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.

Highlighted
MVP
MVP

Re: Text date as date

Have you tried a format like

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

?

Highlighted
Creator II
Creator II

Re: Text date as date

Hi Paul,

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

Re: Text date as date

HI

Try like this

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

View solution in original post

Highlighted
Partner
Partner

Re: Text date as date

Yep no luck

Highlighted
MVP
MVP

Re: Text date as 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?

Highlighted
Not applicable

Re: Text date as date

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.



Highlighted
MVP
MVP

Re: Text date as date

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?

Highlighted

Re: Text date as date

Hi Stefan,

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