topic Difference between two dates in QlikView

Difference between two dates

Wed, 22 Feb 2012 16:40:08 GMT

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.

Difference between two dates

fernando_tonial — Wed, 22 Feb 2012 16:43:07 GMT

You can try this.

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

Best Regards.

Tonial.

Difference between two dates

Wed, 22 Feb 2012 16:54:29 GMT

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

in order to get 2 hours less

Difference between two dates

Wed, 22 Feb 2012 17:08:17 GMT

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.

Difference between two dates

CELAMBARASAN — Wed, 22 Feb 2012 17:10:50 GMT

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

Difference between two dates

Wed, 22 Feb 2012 17:13:16 GMT

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

Re: Difference between two dates

fernando_tonial — Wed, 22 Feb 2012 17:19:15 GMT

try this.

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

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

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

Best Regards.

Tonial.

Difference between two dates

Thu, 23 Feb 2012 12:16:08 GMT

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

Difference between two dates

Thu, 23 Feb 2012 12:38:36 GMT

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

Difference between two dates

Thu, 23 Feb 2012 12:51:16 GMT

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

Difference between two dates

Thu, 23 Feb 2012 13:02:14 GMT

Did you try using Timestamp(Num(Date)-(2/24),'YYYY-MM-DD hh:mm:ss') ?

Difference between two dates

fernando_tonial — Thu, 23 Feb 2012 13:15:01 GMT

You can change the initial settings in the script.

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

for

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';