Date Formatting

[wdchristensen]

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. 

[wdchristensen]

I just confirmed the data is actually a date.

Isnum(MyDate) = -1 

[wdchristensen]

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/Interpreta...

[dwforest · Accepted solution]

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'))

[wdchristensen]

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. 

[wdchristensen]

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

[wdchristensen]

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

[wdchristensen]

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! 

[dwforest]

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

[wdchristensen]

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