Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gadwinjer
Partner - Contributor III
Partner - Contributor III

No time value from a DateTime field

Hello,

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, 😎 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 ?

8 Replies
Chanty4u
MVP
MVP

did you try this in script?

Load *,

Date(Date#(YourDateField,' MM/dd/yyyy hh:mm:ss'),'hh:mm:ss') as new date

MK_QSL
MVP
MVP

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

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
gadwinjer
Partner - Contributor III
Partner - Contributor III
Author

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

MK_QSL
MVP
MVP

Date(Floor(TimeStamp#(YourDateTimeField,'YYYY-MM-DD hh:mm:ss'))) as Date,

Time(Frac(TimeStamp#(YourDateTimeField,'YYYY-MM-DD hh:mm:ss'))) as Time

gadwinjer
Partner - Contributor III
Partner - Contributor III
Author

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.

gadwinjer
Partner - Contributor III
Partner - Contributor III
Author

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

gadwinjer
Partner - Contributor III
Partner - Contributor III
Author

Finally, I solved my problem by spliting the date in the SQL select statement :

SQL SELECT

    substr(myDate, 0, 11) as myDate, // yyyy-MM-dd

    substr(myDate, 12,19) as myTime, // hh:mm:ss
FROM
     myTable


myTime contains the correct value of the time of my SQLite date field.