Date and Datetime issues

[musketeers]

Hi,

I‌ have a table field which data type is DateTime. Till last week it has only DATE data like 2017-01-01 00:00:00.

A‌ll data in this field was like this. I was pulling this information in Qlikview using Date function to get only Date part and leaving the time protion.

F‌rom current week, this field in SQL table is also storing HOUR information. So now the data is like 2017-01-01 12:00:00.

B‌ut in qlikview I am still using Date function to get only Date part and leaving the time portion.

M‌y Date function is like: DATE(START_DATE, "DD-MM-YY")

N‌‌ow my question is:

After the hour part is added in SQL Table, Qlikview is not displaying the correct result in charts.

W‌hat I found is that for a particular product in SQL Table, we have data for 2nd July 2017 to 6th JULY 2017

B‌ut Qlikview is displaying data for only 3 July, 4 July and 5 July in chart , but it has loaded all rows from SQL table.

I‌ am not able to understand why Qlikview is not taking data for 2 July and 6 July while calculating data in chart.

I‌ have checked the log file and it says that Qlikview has loaded all rows from SQL table.

P‌lease help me understand why this behaviour. 

[pipuindia99]

Hi Try to use Date# and date like Date(Date#(fieldname,'format'),'format')

[stigchel · Accepted solution]

Using the Date function you don't 'leave' the time portion, it is a formatting function only, please read

The Date Function

[Kushal_Chawda]

Set the timestamp variable format as per the format of START_TIME

SET TimestampFormat='YYYY-MM-DD hh:mm:ss';

Now you can try this

LOAD ..

date(floor(START_DATE),'DD-MM-YY') as START_DATE

...

[musketeers]

Thanks everyone for help.

I have read this article and found the internal working of Date function.

Now I got, even when I was using the Date function to eliminate the Time part from date field, the internal calculation of Qlikview was still using the time part but the front part was displaying only Date part.

Thanks for this useful link.

[jasmeenkr]

I am getting error while using expression Date(Datetime,'YYYYMMDD') where DateTime =9/12/2019 12:00:00 AM.

It gives me number 43725 . I want to display 20190912.