DateDiff Function
30 September 2010, 1:41 pm
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. |