topic Re: Get exact hours between dates in QlikView

Get exact hours between dates

sgonzalez_adaso — Sat, 16 Nov 2024 21:04:22 GMT

Hi all,

I'm doing an incremental reload in my document.
For that reason, the filter dates I use are not always exact, but I need to get for the SQL script that I throw the exact time like "YYYY/MM/DD HH:00:00"

Example: I make my reload at 2019/04/11 09:43:26 and I want to use the date 2019/04/11 09:00:00.

I am trying to use the function ROUND like:

Timestamp (round (Time, (1/24)))

but I can not match it with the date. (Format YYYY/MM/DD HH:00:00)

Any suggestion?

Regards,

Sergio

Re: Get exact hours between dates

PrashantSangle — Thu, 11 Apr 2019 09:25:51 GMT

Simply use TimeStamp()
TimeStamp('YourDateField','M/DD/YYYY hh:00:00')

Re: Get exact hours between dates

sgonzalez_adaso — Thu, 11 Apr 2019 11:37:34 GMT

It isn't working as expected.

Actually, I have two timestampts on format "YYYY-MM-DD HHmm:ss".

Lets say they are called StartDateTime and EndDateTime.

So I create two variables to transform and use that dates for my query to SQL.

LET v.HourStartDatetime = NULL();

LET v.HourEndDatetime = NULL();

v.HourStartDatetime = Date(timestamp#(v.HourStartDatetime, 'YYYY-MM-dd HH:00:00'));

v.HourEndDatetime = Date(timestamp#(v.HourEndDatetime, 'YYYY-MM-dd HH:00:00'));

Using this approatch the timestamp I get is empty.

Re: Get exact hours between dates

PrashantSangle — Thu, 11 Apr 2019 15:12:50 GMT

what is your actual script.
As per logic you posted it will give null

Re: Get exact hours between dates

sgonzalez_adaso — Thu, 11 Apr 2019 15:16:24 GMT

I make a mistake on the copy/paste, my fault.

LET v.HourStartDatetime = NULL();

LET v.HourEndDatetime = NULL();

v.HourStartDatetime = Date(timestamp#('2018-11-27 19:42:25', 'YYYY-MM-dd HH:00:00'));

v.HourEndDatetime = Date(timestamp#('2019-01-02 19:42:25' 'YYYY-MM-dd HH:00:00'));

Re: Get exact hours between dates

PrashantSangle — Thu, 11 Apr 2019 15:20:24 GMT

LET v.HourStartDatetime = Timestamp(timestamp#('2018-11-27 19:42:25','YYYY-MM-DD hh:mm:ss'),'YYYY-MM-DD hh:00:00');
LET v.HourEndDatetime = Timestamp(timestamp#('2019-01-02 19:42:25' 'YYYY-MM-DD hh:mm:ss'),'YYYY-MM-DD hh:00:00');

Regards,

Re: Get exact hours between dates

sgonzalez_adaso — Thu, 11 Apr 2019 16:05:10 GMT

Quite simple and working perfectly.

Sir, you are my saviour.