Skip to content
 

DateDiff Function

Uses

The DateDiff function is used to determine the amount of time between two datetimes. You control the interval used in measuring the different (years, hours, etc.)

  • Show the days between two datetimes.
  • Show hours a ticket has been open.

ToText

Syntax Variable Optional
DateDiff (Intervaltype, startDateTime, endDateTime) IntervalType The interval type to use. This is a string value that must use the defined forms (see table below.)
y The number of decimal places to include in result (optional). The value will be rounded to that decimal place.
z The character to use as the thousands separator. If you don’t specify one, it will use your application default. (Optional.)
w The character to use as the decimal separator. If you don’t specify one, it will use your application default. (Optional.)

ToText with optional day of week constraint

Syntax Variable Optional
DateDiff (Intervaltype, Date1, Date2, firstDay) IntervalType The interval type to use. This is a string value that must use the defined forms (see table below.)
y The number of decimal places to include in result (optional). The value will be rounded to that decimal place.
z The character to use as the thousands separator. If you don’t specify one, it will use your application default. (Optional.)
w The character to use as the decimal separator. If you don’t specify one, it will use your application default. (Optional.)
firstDay The first day of week to use. The default is Sunday (crSunday.)

Interval Type Values

Interval Type Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weeks
If Date1 is on a Tuesday, counts the number of Tuesdays between Date1 and Date2 not including the initial Tuesday (Date1). (It will count the last Tuesday if Date2 falls on a Tuesday.)
ww Number of firstDays between dates

Almost identical to w, except it counts the day you specify rather than the day Date1 falls on.

h Hours
n Minutes
s Seconds

Examples

Example Result Notes
DateDiff(“d”, #3/10/2010#, #3/12/2010#) 2
DateDiff(“yyyy”,#1/1/2010#,#12/31/2010#) 0 yyyy only looks at the years; even though these dates are nearly a year apart, the result is 0.
DateDiff(“yyyy”,#12/31/2009#,#1/1/2010#) 1 yyyy only looks at the years; even though these dates are one day apart, the result is 1.
DateDiff(“q”,#1/1/2010#,#2/1/2010#) 0 The dates are in the same quarter.
DateDiff(“q”,#3/1/2010#,#4/1/2010#) 1 The dates are in different quarters.
DateDiff(“m”,#3/1/2010#,#3/31/2010#) 0 m only looks at the month; even though these dates are 30 days apart the result is 0.
DateDiff(“m”,#3/31/2010#,#4/1/2010#) 1 m only looks at the month; even though these dates are 1 day apart the result is 1.
DateDiff(“w”,#3/31/2010#,#5/1/2010#) 4 3/31/10 is a Wednesday. DateDiff counts the number of Wednesdays between the two dates, not counting 3/31/10.
DateDiff(“ww”,#3/31/2010#,#5/1/2010#,crThursday) 5 3/31/10 is a Wednesday. DateDiff counts the number of Thursdays between the two dates, because 3/31/10 is not a Thursday, it counts the Thursday in that week, resulting in a higher number than the previous example.

Leave a Reply