date difference

[#1]

Good day,
I would like to have a date diff function in an expression. im getting a weird answer. say

date

(2010/10/10) -date(2010/10/11), im getting 1.82727272. does anyone know whats wrong here.

Regards.

)

[boorgura · Accepted solution]

Try using:

date#('2010/10/11', 'YYYY/MM/DD') - Date#('2010/10/10', 'YYYY/MM/DD')

It gives the number of days difference.

You can also try the interval function.

Let me know if it works.

[#3]

Hi

Dates are stored with days in the integer part, time in the fractional part of the number. The example calculatation you show has obviously the time in at least one of the values included. You can get rid of the time (fractional part) with floor, ceil or round, depending on what you want to achieve.

You can also just floor the result to get the number of days but it will return 0 for a part of a day difference.

Regards

Jürg

[#4]

Hi Guys,

if i try to add an average function on this date format, it just seems not to work, Does anyone know whats wrong with this expression.

avg

(if((not isnull(DateCreated) and not isnull(DateIssued)),(date#(DateIssued,'YYYY/MM/DD')-date#(DateCreated,'YYYY/MM/DD')),null()))

Thank you,

[#5]

thank you all guys, the floor function seem to have done the trick,

I dont understand why the date#(Date,'format') didnt work on running data. if i use type in the dates it works but not if i use a column name.

This is working

avg

(if((not isnull(DateCreated) and not isnull(DateIssued)),(floor(DateIssued)-floor(DateCreated)),null()))

Thanks guys.

[#6]

use diff function in macro editor

function datediff2(d1,d2)
datediff2=datediff("D",d1,d2)
end function

And than call this function in qlikview editor

datediff2(Date1,Date2) it no of day between date1 and date2

Regards

Ashish