Converting field to date

[RogerG]

Hello all,  

I am having an issue with dates, as is everyone.  

I have a date loaded from a QVD, to which I don't have direct access.  

When I load the date, it shows in the Data Model viewer as an integer.  

After much searching and understanding, I got desperate and tried everything

[PA] :
NoConcatenate
Load 
 Date(num#([LBE Start Date])),
 Date(num([LBE Start Date])),

 Date#(num#([LBE Start Date])),
 Date#(num([LBE Start Date])),

 Date([LBE Start Date]),
 Date#([LBE Start Date]),

 Date(num#([LBE Start Date]),'DD-MMM-YYYY'),
 Date(num([LBE Start Date]),'DD-MMM-YYYY'),

 Date#(num#([LBE Start Date]),'DD-MMM-YYYY'),
 Date#(num([LBE Start Date]),'DD-MMM-YYYY'),
 Date#([LBE Start Date],'DD-MMM-YYYY'),
 Date([LBE Start Date],'DD-MMM-YYYY'),

 Date(Date#(num#([LBE Start Date]),'DD-MMM-YYYY')),
 Date(Date#(num([LBE Start Date]),'DD-MMM-YYYY')),
 date(date#([LBE Start Date],'DD-MMM-YYYY')),
 
 Date(Date#(num#([LBE Start Date]),'DD-MMM-YYYY'),'DD-MMM-YYYY'),
 Date(Date#(num([LBE Start Date]),'DD-MMM-YYYY'),'DD-MMM-YYYY'),
 date(date#([LBE Start Date],'DD-MMM-YYYY'),'DD-MMM-YYYY'),
 
 Date#(Date(num#([LBE Start Date]),'DD-MMM-YYYY')),
 Date#(Date(num([LBE Start Date]),'DD-MMM-YYYY')),
 date#(date([LBE Start Date],'DD-MMM-YYYY')),
 
 Date#(Date(num#([LBE Start Date]),'DD-MMM-YYYY'),'DD-MMM-YYYY'),
 Date#(Date(num([LBE Start Date]),'DD-MMM-YYYY'),'DD-MMM-YYYY'),
 date#(date([LBE Start Date],'DD-MMM-YYYY'),'DD-MMM-YYYY'),
*;
Load * resident [TEMP PA];

I am still not able to pull a date field from this.  In QS, my calendar date picker is not recognizing anything as a date.

Maybe I should give up coding 🙂

[rubenmarin]

Hi, if it's an integer, just Date(fieldName) should work.

Try loading this field in an empty app and add as a filter or in a table as dimension, if the values are left-aligned is a text, if it's right aligned it's a number.

All dates are numbers internally, each unit is a day: date(0) is 30/12/1899, Date(1) is 31/12/1899, Date(-1) is 29/12/1899 and 30/06/2022 is 44742. Decimals are used to set time so 44742.5 is 30/06/2022 at 12:00.

If the field looks like a number but is left aligned you need to onvert to number using Num#(), this depends on the formt of the number, you can post an image on how the data is shown on a table dimension.

[Or · Accepted solution]

One other thing - if your dates are in the distant future or past, Qlik might not automatically flag the field as a date. In that case, you'll need to manually tag the field as a date, if memory serves the syntax is:

Tag Field [LBE Start Date] with '$date';

If that's not the issue, Ruben's post contains all of the steps to further investigate your issue.

[RogerG]

they are coming into the table as Dates, however the date picker and data model viewer disagree

this is my entire datamodel, this one date, everything else is commented out to see why the conversion is not functioning

[RogerG]

Thanks Or, this solved my issue, must be those historical dates