Date is display as a number no matter which format

[fortaleza40]

I am having a date coming from a postgres database using Native SQL of Qlik:

"1901-12-24 12:00:00-06"

When i apply this code to the date is coming empty:

date(floor(my_date),'MM/dd/YYYY')

So i tested using this formula in my query and i can see the date correct in the database console but in Qlik QVD and Report i get the number 754:

TO_CHAR((my_date)::TIMESTAMP::DATE,'MM/dd/YYYY')

I also found that if i do this:  my_date::DATE i get the number 754 too

also i tested using the substring function because i only need the date in varchar without the time values:

substring(my_date::VARCHAR,1,10)::VARCHAR

and i get a number 754, and this is happening only with this date, all the other records display as dates, so, i am trying to understand this weird behaviour of Qlik. Help.

[Rodj]

You might want to take a look at this section in the help.

Qlik stores dates as numbers, so the numbers you are seeing look to me to be probably appropriate for the timestamp example you've supplied. You just now need to display it in a date format that is appropriate to you. if you wrap that field you've loaded in a Date() function in either the load script or an expression for a dimension or measure you should be able to get the result you want. The Qlik engine  typically makes a pretty good fist of interpreting strings and date fields from different sources as dates but sometimes gets it wrong.

When you look at the various interpretation functions you'll see those ending in '#' (e.g. Date#())tend to be ones that interpret a string into a date and or time and those that don't (e.g Date()) being for formatting.

I would try loading your data as timestamp#(my_date, 'YYYY-MM-DD hh:mm:ss-ff') to have Qlik understand is a timestamp. You could wrap that in a Date(my_date[result]), 'MM/dd/YYYY') to get the nice date presentation you're after.

Your substring approach is kind of close too, you could try something like this in the LOAD section of your script (i.e., not the Postgres interpreted SQL section): Date(substring(my_date, 10), 'YYYY-MM-DD').

A good tip is to look at the data model that Qlik produces and inspect the field to see how it has tagged the field. This will usually clue you in as to whether the Qlik engine is interpreting the field as a datetime or a string.

[dwforest]

In order for Qlik to recognize a Date, make sure your default Date format matches the data.

This is done in your load script, for the data you show, it should be:

SET DateFormat="YYYY-MM-DD"

If you want the time, set the Timeformat:

SET TimeFormat="YYYY-MM-DD HH:mm:ss"

Alternatively, you can override each date in the load:

Load

date(floor(date#(my_date,'YYYY-MM-DD')),'MM/dd/YYYY')

from table

[Rodj]

Just remember to get your case right in the format expressions, I've got it wrong in my earlier post;

MM = months

mm = minutes

In general time formats are lower case and date formats are upper. This is easy to get wrong and can screw up your conversions without raising any kind of error.

[fortaleza40]

I tested the DateFormat and nothings change, also i set the time with similar result, when i use the date() function i get a null result, i am getting the 724 number and still not understand why qlik is doing this for a particular date

[Rodj]

Any chance you could supply a small example app, even if it only has a few records from that one field in it?

[fortaleza40]

Hello Rodj,

I cant, but this is the only date with issues, "1901-12-24 12:00:00-06" i am doing an app in qlik sense desktop with manual entry of that date to test

[Rodj · Accepted solution]

It's kind of interesting that that is the only date causing an issue, unfortunately I don't have the means at hand to replicate your situation exactly. I'm a little unclear as to whether you are trying to convert the date in your SQL or in your Load statement. I suggest doing it in your Load statement. Here's some examples using a different date form a Teradata DB:

[Current_Timestamp_Table]:
LOAD
CurrentEDWTimestamp,
Date#(CurrentEDWTimestamp) as DateInt,
Date(CurrentEDWTimestamp) as Date,
Timestamp#(CurrentEDWTimestamp) as TSInt,
Floor(CurrentEDWTimestamp) as Floor,
Date(Floor(CurrentEDWTimestamp), 'DD-MM-YYYY') as DateFloor,
Date#(Floor(CurrentEDWTimestamp), 'DD-MM-YYYY') as DateIntFloor;
SQL
SELECT CURRENT_TIMESTAMP AS CurrentEDWTimestamp;

The results look like this:

Note that my default date format is DD/MM/YYYY and I'm loading a field that is of a timestamp type, which I assume you are. If you are in fact loading a string you'll get different results as Qlik is using that type information to help it interpret the data. Observe how the Date# and Floor statements result in Qlik storing the date as a number.  This is ok, I just need to use a Date(<field name>, 'DD/MM/YYYY) style statement to get the presentation I want in the app.

Hopefully some of that gives you an idea.

Cheers,

Rod

[fortaleza40]

The solution was use this:

Date(Floor(Date Field)

In another part of the code, looks like the Floor function fix the problem of the dates.