No time value from a DateTime field

[gadwinjer]

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 ?

[Chanty4u]

did you try this in script?

Load *,

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

[MK_QSL]

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.

[gadwinjer]

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]

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]

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]

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]

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.