1 2 Previous Next 20 Replies Latest reply on May 9, 2019 1:18 PM by Michel Caissie

# Difference between dates "Day" fields

I have 2 date time fields 4/19/2019 7:49 and 4/18/2019 23:59.  Using my Datediff function of datediff ('hour', 4/18/2019 23:59. 4/19/2019 7:49)/24 I end up with a difference of .125.  I simply want to distinguish the number of days between the day fields and if the difference is greater than 0.  I was using Datediff "day" initially but when I had used that function and ran into data like 4/10/2019 7:17 and 4/9/2019 23:59 it counts it as 0 when I would want that calculated as 1 (I assume because it is not a full 24 hours).

• ###### 1. Re: Difference between dates "Day" fields

In both these examples I am looking for it to =1.   If I have two data fields with same "Day" but the time is later I would want that to =0

• ###### 3. Re: Difference between dates "Day" fields

Jason,

before computing the datediff , you can datetrunc  your date  to remove the   hours, min, sec

datetrunc('day', [date]).    This will be the new date used in the datediff.

Michel

• ###### 4. Re: Difference between dates "Day" fields

CEILING( DATEDIFF('hour', [Order Date], [Ship Date])/24 )

• ###### 5. Re: Difference between dates "Day" fields

I see the "1" in the screenshot but it is returning 0 for me, should I change the data type or convert to continuous?

• ###### 6. Re: Difference between dates "Day" fields

data type right now is Date & Time

• ###### 7. Re: Difference between dates "Day" fields

It appears that on the datetrunc it changed 4/18/2019 23:59 to 4/19/2019 0:00

• ###### 8. Re: Difference between dates "Day" fields

Try the Ceiling function and see if it works

• ###### 9. Re: Difference between dates "Day" fields

The Ceiling function brought me back to counting the 4/19/2019 7:49 and 4/18/2019 23:59 scenario correctly as "1" but it is also counting the same date with later time as "1"

• ###### 10. Re: Difference between dates "Day" fields

--It appears that on the datetrunc it changed 4/18/2019 23:59 to 4/19/2019 0:00

It works fine on my side.

You can also try the date() function

datediff('day',   date( [date1]), date( [date2]) )

Can you upload a  .twbx.   It may be   version related.

• ###### 11. Re: Difference between dates "Day" fields

attached

• ###### 12. Re: Difference between dates "Day" fields

Please extract your data nd reattach workbook. What was wrong if you change date type to Date and then use Datediff as I did?

• ###### 13. Re: Difference between dates "Day" fields

Jason,

You will need to extract your data first, before saving  .twbx.   Because we cannot connect to your database.

(right-click your datasource and select extract data)

• ###### 14. Re: Difference between dates "Day" fields

I just tried that and It reverses the problem and does not count when the "Day" of the date field is different but under 24 hours, when it changes it to a Date only field the 4/18 11:59 becomes 4/19

1 2 Previous Next