I'm working on Qlik Sense 3.1 RC2 (and my bug also occured on 3.1 RC1). I worked on some other versions and didn't have this bug :
When I load my data from a SQLite file (using ODBC) or from a CSV file, I can not extract time from my datetime fields (format : MM/dd/yyyy hh:mm:ss).
I tried these in the loader :
- right(field, 8) as fieldname => only date number value is retrieve (42000...)
- date(field, 'hh:mm:ss') => 00:00:00
I never had this kind of bug. I tried to reinstall (that's why I tested it on RC1 and RC2 of 3.1 version) and it didn't change anything.
Is it a bug or does something has changed ?
You can use something like below..
Date(Floor(TimeStamp#(YourDateTimeField,'DD/MM/YYYY hh:mm:ss'))) as Date,
Time(Frac(TimeStamp#(YourDateTimeField,'DD/MM/YYYY hh:mm:ss'))) as Time
If you're using a csv file then use the date#, time# and/or timestamp# functions to convert strings into date, time or timestamps.
If you're using an SQLite datebase then you'll be working with numbers, not strings. Using string functions like right on a number can give funny results if you don't know what numbers you're dealing with. Time(field,'hh:mm:ss') should show you the time of the datetime value. Time(Frac(field),'hh:mm:ss') will first remove the date part and only work with the time part.
Hello, thank you to try to help me .
I tried all solutions you all told with no success, I only get null values from the time field. (or DD/MM/YYYY 00:00:00 from the date).
From the CSV, I'm confused because it wasn't a bug, the extract we gave me only had date value from the datetime field.
From the SQLite file... I don't know what to do. Here is an example of value from this field : 2016-11-27 01:00:13 . none of your solutions bring me more that 00:00:00 or null value...
I will edit a csv file with a datetime value structured like the sqlite field to see if I can do something with it...
Hello, thank you but I already tried your answer...
Date(Floor(TimeStamp#(SUBMISSIONDATE,'YYYY-MM-DD hh:mm:ss'))) as SUBMISSIONDATE,
Time(Frac(TimeStamp#(SUBMISSIONDATE,'YYYY-MM-DD hh:mm:ss'))) as SUBMISSIONTIME,
And from both, I retrieve null value.
Without surprise, everything work fine with the CSV file. I think my problem comes from the odbc driver... I will try with JDBC and the qlik JDBC connector.
I won't be able to test on JDBC unfortunately. So I exported my sqlite database as CSV file with DB Browser for SQLite and, as expected, everything work fine now...
Does anybody already got this issue with SQLite using ODBC ?
Thank you for your concern
Finally, I solved my problem by spliting the date in the SQL select statement :
substr(myDate, 0, 11) as myDate, // yyyy-MM-dd
substr(myDate, 12,19) as myTime, // hh:mm:ss
myTime contains the correct value of the time of my SQLite date field.