Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Difference between two dates

Hi!,

I have two dates and i want to calculate the difference between the two in hours. Not sure which function i have to use or how to go about it . Can somebody throw some light on this.

Also in other requirement i want to subtract 2 hrs from my time stamp.

The format in which in which i am getting date and time from SQL is '2012-02-22 13:20:00.000'

Thanks in advance.

11 Replies
fernando_tonial
Employee
Employee

You can try this.

Interval( End - Start, 'hh:mm:ss' ).

Best Regards.

Tonial.

Don't Worry, be Qlik.
Not applicable
Author

Interval(End - Start - (2/24), 'hh:mm:ss')

in order to get 2 hours less

Not applicable
Author

for 2 hr less i dont want the difference in hrs. i just want to subtract my timestamp by 2 hrs and then compare it with other time stamp. so i just need to subtract 2 hrs and want the result in same format.

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Try with interval function.

     =interval(EndDate-StartDate,'hh') which will give you hrs difference.

     For subtracting 2 hrs use expr below

     =Timestamp(Num(Date)-(2/24)) which will give you subtraction of 2 hrs.

Hope it helps

Celambarasan

Not applicable
Author

QV uses numbers to represent timestamps, so is the same to substract 2 hours to the total or to the last timestamp.

fernando_tonial
Employee
Employee

try this.

interval( (End-Num('02:00'))-Start, 'hh:mm:ss' )

OR

Time(interval( End-Start, 'hh:mm:ss' )-Num('02:00'))

OR

Time(interval( End-Start, 'hh:mm:ss' )-(2/24))

Best Regards.

Tonial.

Don't Worry, be Qlik.
Not applicable
Author

Hi! i am getting my date and time in 12 hr format i.e 23/02/2012 5:44:03 PM i want it in 24 hr format like

2011-09-11 10:38:48.000

Not applicable
Author

Hi,

If you're using the time function you can add formatting

using previous example

Time(interval( End-Start, 'hh:mm:ss' )-(2/24),'hh:mm:ss') should give you 24hours format.

Regards

Not applicable
Author

by Using "Timestamp(Num(Date)-(2/24))"  using date as "2012-02-22 15:19:00.000"

i am getting two hr difference but i am getting the result in AM/PM format like    22/02/2012 1:19:00 PM i want the result as 22/02/2012 13:19:00