Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
sgonzalez_adaso
Partner - Contributor III
Partner - Contributor III

Get exact hours between dates

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

Labels (4)
1 Solution

Accepted Solutions
PrashantSangle

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,
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

6 Replies
PrashantSangle

Simply use TimeStamp()
TimeStamp('YourDateField','M/DD/YYYY hh:00:00')
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
sgonzalez_adaso
Partner - Contributor III
Partner - Contributor III
Author

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.

 

 

 

 

PrashantSangle

what is your actual script.
As per logic you posted it will give null
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
sgonzalez_adaso
Partner - Contributor III
Partner - Contributor III
Author

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'));

PrashantSangle

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,
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
sgonzalez_adaso
Partner - Contributor III
Partner - Contributor III
Author

Quite simple and working perfectly.

Sir, you are my saviour.