Get exact hours between dates

[sgonzalez_adaso]

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

[PrashantSangle]

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

[sgonzalez_adaso]

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

[sgonzalez_adaso]

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 · Accepted solution]

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,

[sgonzalez_adaso]

Quite simple and working perfectly.

Sir, you are my saviour.