Calculate number of days between two date fields?

[#1]

Hi,

I have two dates in my table, Shipped Date and order Date, I want to calculate the number of Days between these two tables coming from the table. if I hardcode the value correctly I am able to get the value but if I am putting field Name. I am not getting and it giving some random value .I have seen many posts on this but couldn't able to check with the right post.

[maxgro · Accepted solution]

usually is just the difference

[Shipped Date] - [Order Date]

if you still have problem, try to post a small example (.qvw) of your problem

[Colin-Albert]

As Massimo says it should just be the difference between the two fields.

Can you check that the data in the two fields is loaded correctly as a date field.

If you put each date field in a list box, are the dates left or right justified?

If the dates are correctly loaded as date fields (dual values), the dates should show as right justified in your list box.

Have a look at this post for more help on dates & formatting Get the Dates Right

[Anonymous]

you could also use num([Shipped Date]) - num([Order Date]) if your date formats are in different

[MarcoWedel]

sounds like one of your date fields is not dual (i.e. it has no numerical value, just text).

check in the table viewer or with the num(yourdatefield) function.

regards

Marco

[#6]

Hi All,

Thanks for your replies !!!..

I went and checked in the Edit transformation Step and it showing some numbers like 8897.00 instead of Date mentioned in the Excel file. Hope there is some mistake while loading.?

[avinashelite]

yes, convert that to proper date  format ...

like date(field,'MM-DD-YYYY') as field

and you can use interval function to get the number of days

eg:

interval( A-B, 'D hh:mm' ) where A=97-08-06 09:00:00 and B=96-08-06 00:00:00 returns:

String	365 09:00
Number	365.375

[jagan]

Hi,

While load data from Excel convert your date fields into date format using Date() like below

LOAD

Date(DateField1) AS DateField1,

Date(DateField2) AS DateField2,

Num(Date(DateField2) - Date(DateField1)) AS DaysDiff

'

'

'

FROM FileName.xls;

Hope it helps you.

Regards,

Jagan.

[#9]

Got the output Thanks for all your suggestions !!!

[avinashelite]

Hi Sreeharsha,

Cool you got the answer, can you please mark the correct answer and close this thread .