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])

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)

See NOW and NOWDATE.

Tip: 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))