DATEDIFF
The DATEDIFF function calculates the number of date parts (days, weeks, and so on) between two specified dates, times, or timestamps.
Note: TDV supports the two parameter formats that supported data sources use. Note that the order of startdate and enddate is swapped in the two formats.
Syntax
DATEDIFF (datepart, startdate, enddate)
DATEDIFF (enddate, startdate)
Remarks
• The first argument specifies the datepart for which to return an integer indicating the difference—for example, 1 (day), 4 (years), and so on.
• TDV supports these datepart keywords:
YEARS | YEAR | YYYY | YY |
QUARTERS | QUARTER | QQ | Q |
MONTHS | MONTH | MM | M |
WEEKS | WEEK | WW | WK |
WEEKS_US [an artificial date part for use in TDV only; see example 1 below] |
DAYS | DAY | DD | D |
HOURS | HOUR | HH | |
MINUTES | MINUTE | MI | M |
SECONDS | SECOND | SS | S |
MILLISECONDS | MILLISECOND | MS | |
• The other two arguments (startdate and enddate) are chronological values.
• TDV by default calculates DATEDIFF according to the ISO standard (using Monday as the first day of the week). Databases that are locale-aware (for example, Sybase) calculate according to the local standards they are configured to implement—for example, the US standard (which uses Sunday as the first day of the week). This variance in implementation can cause week-counts calculated in the data source to differ from week-counts calculated in TDV.
• WEEKS_US is an artificial datepart that makes TDV calculate DATEDIFF according to the US standard instead of the ISO standard. WEEKS_US should not be pushed to a data source, because it will be rejected there.
• Sybase produces correct (standard) results for year, month, day date parts and incorrect results for hour, minute, second date parts. TDV produces correct results for all six.
Example 1
Calculate the difference in weeks between a Friday and the following Sunday:
DATEDIFF ('WEEK', DATE '2014-04-25', DATE '2014-04-27')
According to US standard, the week starts with a Sunday; therefore, the two dates belong to different weeks (Sunday starts a new week), and so a locale-aware database produces 1.
According to ISO standard, the week starts with a Monday; therefore, Friday and Sunday belong to the same week (starting the prior Monday), so TDV produces the result 0.
If you use the artificial date part WEEKS_US, TDV produces the result 1:
DATEDIFF (‘WEEKS_US’, DATE ‘2014-04-25’, DATE ‘2014-04-27’)
Example 2
Calculate the difference in years between August 15, 2009 and December 31, 2012:
DATEDIFF ('year', date '2009-08-15', date '2012-12-31')
TDV returns 3 by counting the year intervals as follows:
[1] January 1, 2010 + [2] January 1, 2011 + [3] January 1, 2012 = 3
The months between January 1, 2012 and December 31, 2012 are ignored, because the datepart specified is YEAR, and only the start of each year is counted.