topic Re: Date Formatting in App Development

Date Formatting

wdchristensen — Tue, 21 Dec 2021 21:30:18 GMT

When I add a date field to a filter pane in Qlik Sense it shows up in an undesirable format (yyyy-mm-dd). My data is being pulled from date fields in SQL Server it formats the data in format? It is almost like I am inadvertently converting the date to a string before it enters my application. How can I get my filter to show the date as I intend to see it (m/d/yyyy) without converting it to a string? Any help would be appreciated.

Re: Date Formatting

wdchristensen — Fri, 04 Jan 2019 19:15:15 GMT

I just confirmed the data is actually a date.

Isnum(MyDate) = -1

Re: Date Formatting

wdchristensen — Fri, 04 Jan 2019 19:53:33 GMT

Hey Buddy,

Qlik Sense is strange in the handling of dates and I have no idea why it does what it does. That being said, I suggest casting the dates on your load script even though they are already dates in SQL Server. This will allow you to use the dual function to view the desired string version of the date.

Date(MyDate, 'M/D/YYYY') as MyDate

And if you have a time on that date you might think lets add the keyword "time" to the keyword "date" and make use of a "DateTime" function. That kind of thinking leads to trouble, stop it immediately. Look for an altogether different term like timestamp and you just might be on to something. Happy coding!

https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/Scripting/InterpretationFunctions/timestamp_hash.htm

Re: Date Formatting

dwforest — Sat, 05 Jan 2019 00:05:51 GMT

You need to set the DateFormat to match that returned by SQL Server to get it to recognize it as a date and store the dual value of the number and the string.
After load you can set the format back to M/D/YYYY to have it display that way.
Alternately for every date in load script replace just yourdatefield with this "conversion"
Date(Date#(yourdatefield,'YYYY-MM-DD'))

Re: Date Formatting

wdchristensen — Mon, 07 Jan 2019 15:06:20 GMT

Thanks dvforest for the suggestion. I will give it a shot and let you know how it goes.

The attached document seems to provide an explanation.

Re: Date Formatting

wdchristensen — Tue, 08 Jan 2019 15:17:06 GMT

Tried to set date / datetime as follows

// start of script

SET DateFormat='YYYY-M-D';

SET TimestampFormat='YYYY-MM-DD h:mm:ss[.fff]';

//extract code

SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY';

// works great for date time but not dates

Re: Date Formatting

wdchristensen — Tue, 08 Jan 2019 15:38:55 GMT

I was able to get the date field to import correctly with the statement below.

Date(Date#(myDateField,'YYYY-MM-DD'), 'M/D/YYYY') as myDateField

Re: Date Formatting

wdchristensen — Tue, 08 Jan 2019 15:56:51 GMT

I would really prefer to use the DateFormat and TimestampFormat variables so I don't have to touch each date field to get it in the correct format. I believe the setting the format once at the begining of the script to match SQL Server and once at the end to match my desired format is the best solution. Any idea why this approach only seems to work for datetime fields and not basic date fields? Any help would be greatly appreciated!

Re: Date Formatting

dwforest — Tue, 08 Jan 2019 16:57:50 GMT

Does it work if DateFormat is 'YYYY-MM-DD' instead of 'YYYY-M-D' ?

Re: Date Formatting

wdchristensen — Tue, 08 Jan 2019 17:43:17 GMT

I tried that but it didn't work either....

Re: Date Formatting

dwforest — Tue, 08 Jan 2019 23:38:31 GMT

Are you trying to get the date part from a datetime field? date and timestamp are different in Qlik (and per your original post, if the date format and the timestamp format do not match the data it will make it a string).
To get the date from a timestamp, floor() the value. Dates/Timestamps in Qlik are dual value a number and string display. The number to the right of the decimal is time, so floor() essentially makes it midnight (00:00:00.000).
it is is still not working, example of your script and data is needed.

Re: Date Formatting

wdchristensen — Thu, 10 Jan 2019 22:27:41 GMT

Thanks dwforest for your help.

https://community.qlik.com/t5/Qlik-Sense-App-Development/SET-DateFormat-YYYY-MM-DD-doesn-t-recognize-SQL-Server-dates/m-p/1529188/thread-id/38055/highlight/false#M38056