Date duration Time Calculation

[#1]

Dears,

any idea for how to calculate the duration between two date :

if i have date without time stamp in tables like this

Date1	Date2
1/5/2014	1/21/2014
11/10/2015	11/20/2015
6/18/2014	6/19/2014
8/17/2010	8/25/2010
8/18/2010	8/26/2010
8/19/2010	8/27/2010
8/20/2010	8/28/2010
8/21/2010	8/29/2010
8/28/2010	8/30/2010
8/29/2010	8/31/2010
8/30/2010	9/14/2010
8/31/2010	9/15/2010
9/14/2010	9/16/2010
9/15/2010	9/24/2010
9/16/2010	9/25/2010
9/17/2010	9/26/2010
9/18/2010	9/27/2010
9/19/2010	9/28/2010
9/20/2010	9/29/2010

dears i try your solutions but it is not work this is the QVD

Message was edited by: ahmad khalid

[Anonymous]

Hi

Use interval to get the difference b/w the date in days.

interval(date1- date2,'d') as [Duration]

Thanks

BKC

[PrashantSangle]

Hi,

If it is valid date then

just use num(date2)-num(date1)

gives you difference in days.

Regards

[Clever_Anjos]

Simple as Date2 - Date1 (number of days)

interval(Date2 - Date1) (timestamp)

[Anonymous]

PFA

[#6]

Thank you for all but i missed important thing the two date column in different table  i try to do what you say but it not work and also i tray to take the Date# and calculated but nothing appear also when i but it in variable the same because  this is read from the database

[Anonymous]

then try this in UI .

[#8]

i tray to do it but unfortunately not work .

[ToniKautto]

First you need to make sure the values in your table are actually dates. For example define your format variables to match your incoming format.

SET DateFormat='DD/MM/YYYY';

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

To make a interval calculation in the script, you would need to put the two fields in the same table. This can be done by joining the tables.

In the front end you can do the interval in a straight table, as already suggested with interval(date1- date2,'d')

If you can not sort it out, please provide sample QVW file so that it is possible to see where you go wrong.

[#10]

In the script try the below: What I have done is based on the thoughts put forward and nothing new.

Formatting in the script can be done if you dont want to change the general date settings. also the difference is produced as days you could change that if you desire.

datesTable:

LOAD * INLINE [

    Date1, Date2

1/5/2014,1/21/2014

11/10/2015,11/20/2015

6/18/2014,6/19/2014

8/17/2010,8/25/2010

8/18/2010,8/26/2010

8/19/2010,8/27/2010

8/20/2010,8/28/2010

8/21/2010,8/29/2010

8/28/2010,8/30/2010

8/29/2010,8/31/2010

8/30/2010,9/14/2010

8/31/2010,9/15/2010

9/14/2010,9/16/2010

9/15/2010,9/24/2010

9/16/2010,9/25/2010

9/17/2010,9/26/2010

9/18/2010,9/27/2010

9/19/2010,9/28/2010

9/20/2010,9/29/2010

];

DatesDifference:

load

Date#(Date1) AS StartDate,

Date#(Date2) AS EndDate,

Interval(Date#(Date2,'MM/DD/YYYY')-Date#(Date1,'MM/DD/YYYY'),'d') as datdif

resident datesTable;