DATEDIFF Function
Use the DATEDIFF function to determine the difference between two Date or two Datetime attributes.
DATEDIFF(YEAR|QUARTER|MONTH|WEEK|DAY, start_date_attribute, end_date_attribute)DATEDIFF(YEAR|QUARTER|MONTH|WEEK|DAY|HOUR|MINUTE|SECOND, start_datetime_attribute, end_datetime_attribute)For example, to get the difference in years between two datetime columns:
DATEDIFF(YEAR,[Employees.BirthDate],[Employees.HireDate])| DIFFDATE Year Datetime | BirthDate | HireDate |
|---|---|---|
| 56 | 09/19/1937 12:00 AM | 05/03/1993 12:00 AM |
| 44 | 12/08/1948 12:00 AM | 05/01/1992 12:00 AM |
| 40 | 02/19/1952 12:00 AM | 08/14/1992 12:00 AM |
| 38 | 03/04/1955 12:00 AM | 10/17/1993 12:00 AM |
To get the difference between a date and the current date or date/time, use NOW (for Datetime types) or NOWDATE (for Date types) as the second date argument. For example:
DATEDIFF(DAY,[Order_Details.OrderDate],NOWDATE)
| DIFFDATE NOWDATE | OrderDate |
|---|---|
| 7833 | 11/22/1994 12:00 AM |
| 7839 | 11/16/1994 12:00 AM |
| 7837 | 11/18/1994 12:00 AM |
| 7838 | 11/17/1994 12:00 AM |
| 7830 | 11/25/1994 12:00 AM |
Note: If your dates are in a varchar format, cast the varchar columns into
datetime before using the DATEDIFF function.
DATEDIFF(DAY, DATETIME([YourHierachy.VarcharColumn1]), DATETIME(YourHierachy.VarcharColumn2))