topic Re: Date and Datetime issues in QlikView

Date and Datetime issues

musketeers — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Date and Datetime issues

pipuindia99 — Fri, 07 Jul 2017 13:03:41 GMT

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

Re: Date and Datetime issues

stigchel — Fri, 07 Jul 2017 13:26:06 GMT

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

The Date Function

Re: Date and Datetime issues

Kushal_Chawda — Fri, 07 Jul 2017 13:32:36 GMT

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

...

Re: Date and Datetime issues

musketeers — Mon, 10 Jul 2017 09:47:07 GMT

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.

Re: Date and Datetime issues

jasmeenkr — Wed, 25 Sep 2019 02:38:43 GMT

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.