Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I just confirmed the data is actually a date.
Isnum(MyDate) = -1
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!
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.
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
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
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!
Does it work if DateFormat is 'YYYY-MM-DD' instead of 'YYYY-M-D' ?
I tried that but it didn't work either....