In T-SQL (Transact-SQL), DATEDIFF is a function used to calculate the difference between two dates, expressed in terms of a specified datepart (such as years, months, days, hours, minutes, seconds, etc.).
Here’s the syntax for the DATEDIFF function:
DATEDIFF(datepart, start_date, end_date)
datepart specifies the unit in which the difference should be calculated (e.g., year, month, day, hour, etc.).
start_date is the initial date.
end_date is the date to which the difference is calculated.
For the two time values the following example is, if you want to find the difference in days between two dates:
SELECT DATEDIFF(day, '2024-02-14', '2024-02-20') AS DayDifference;
This would return 6, indicating there are 6 days between the ending date February 14, 2024, and the end date February 20, 2024.
You can use different dateparts like date1 date2 year, month, day, hour, minute, etc., depending on your requirements.
SELECT DATEDIFF(month, '2023-01-01', '2024-01-01') AS MonthDifference;
This would return 12, indicating there is a difference of 12 months between the start date January 1, 2023, and January 1, 2024.
DATEDIFF can be used in various scenarios, such as calculating the age of a person, finding the duration between two events, calculating the tenure of an employee, and so on.
Versions of Datediff By Server
The DATEDIFF function is available in various versions of SQL Server, but there might be some differences in its usage or supported date parts across different versions. Here’s a brief overview:
SQL Server 2000 and later: DATEDIFF function is available in all versions of SQL Server starting from SQL Server 2000. It supports the same date parts mentioned earlier (year, quarter, month, etc.).
SQL Server 2012 and later: Introduced support for the date1 date2 and time data types. This allows for more precise calculations involving dates and times.
SQL Server 2016 and later: Introduced support for datetime2 data type with higher precision than datetime. This allows for more accurate calculations, especially when dealing with fractional seconds.
SQL Server 2017 and later: Support for AT TIME ZONE clause, which allows for conversions between time zones, impacting date calculations involving time zone adjustments.
SQL Server 2019 and later: Introduces support for datetimeoffset data type, which includes a time zone offset. This can impact date calculations involving time zones.
In T-SQL’s DATEDIFF function, the first argument specifies the unit of time you want to calculate the difference in. Here are the different parts you can use:
year: Difference in years.
quarter: Difference in quarters.
month: Difference in months.
dayofyear: Day of the year (1 through 366).
day: Difference in number of days.
week: Difference in weeks.
weekday: Difference in weekdays (Monday through Friday on week off).
hour: Difference in hours.
minute: Difference in minutes.
second: Difference in seconds.
millisecond: Difference in milliseconds.
microsecond: Difference in microseconds.
nanosecond: Difference in nanoseconds.
For example, using DATEDIFF(month, start_date, end_date) calculates the the difference between two date in months between returns the difference between two dates. Similarly, using DATEDIFF(hour, start_date, end_date) calculates the the difference between two date in hours.DateDiff Query examples
One Offf Examples
here are some less common examples of using DATEDIFF:
Difference in decades:
SELECT DATEDIFF(year, '2000-01-01', '2024-01-01') / 10 AS DecadeDifference;
This would return the difference in decades between January 1, 2000, and January 1, which returns the difference in 2024.
Difference in fiscal years:
SELECT DATEDIFF(month, '2023-07-01', '2024-06-30') / 12 AS FiscalYearDifference;
This would return the difference in fiscal years between the starting date of July 1, 2023, and June 30, 2024.
Difference in leap years:
SELECT DATEDIFF(day, '2020-02-29', '2024-02-29') / 365 AS LeapYearDifference;
This would return the difference in leap years between the two date values February 29, 2020, and two date values February 29, 2024.
Difference in work hours (assuming 8-hour workdays):
SELECT DATEDIFF(hour, '2024-02-14T09:00:00', '2024-02-15T17:00:00') / 8 AS WorkDayDifference;
This function would return the difference in work days between February 14, 2024, 9:00 AM and February 15, 2024, 5:00 PM, assuming an 8-hour workday.
Difference in lunar months (rough approximation):
SELECT DATEDIFF(day, '2023-01-01', '2023-12-31') / 29.53 AS LunarMonthDifference;
This would return the difference in lunar months between the two date values January 1, 2023, and the following values December 31, 2023, assuming an average lunar month of 29.53 days.
These examples demonstrate the flexibility of DATEDIFF in calculating differences between two specified dates, based on various units of time, including more unconventional ones.
The result of DATEDIFF can be negative. This happens when the start_date is after the end_date. In such cases, the result indicates a negative difference, implying that the start_date is later than the end_date.
SELECT DATEDIFF(day, '2024-02-20', '2024-02-14') AS DayDifference;
This would return negative value of -6, indicating that ship date of February 20, 2024, is 6 days later than February 14, 2024.
So, negative values are possible and simply indicate that the first date is later than the second date.
Using DATEDIFF() With Table Column Example
You can use DATEDIFF to find the differences between two tables’ column values, particularly if those two columns used are date or datetime types. Here’s a general example:
Let’s say you have two tables, TableA and TableB, each with a column DateColumn.
SELECT
TableA.DateColumn AS DateInTableA,
TableB.DateColumn AS DateInTableB,
DATEDIFF(day, TableA.DateColumn, TableB.DateColumn) AS DateDifference
FROM
TableA
JOIN
TableB ON (some condition, like a primary key or a related column)
In this query:
TableA.DateColumn represents the date values from TableA.
TableB.DateColumn represents the date values from TableB.
DATEDIFF(day, TableA.DateColumn, TableB.DateColumn) calculates the difference in days per week between the dates in TableA and TableB.
The JOIN condition should be specified according to how the two tables are related.
This query will give you a result set showing the date values from both tables along with integer value of the difference in the number of days between the corresponding values. Positive values indicate that the date in TableA is later than the date in TableB, while negative values indicate the opposite.
DateDiff_BIG
DATEDIFF_BIG is an alternative version of the DATEDIFF function that returns the difference between two dates as a bigint data type instead of an int data type.
The regular DATEDIFF function returns an int, which has a maximum value of 2,147,483,647. If the difference signed integer value between two dates exceeds this value, DATEDIFF will return an error or an incorrect result.
DATEDIFF_BIG is introduced to handle cases where the difference between two dates can exceed the maximum value of int. It can handle a much larger range of date differences and is suitable for scenarios where the date range is extensive, such as in financial calculations or historical data analysis.
Here’s the syntax for DATEDIFF_BIG:
DATEDIFF_BIG(datepart, start_date, end_date)
The datepart, start_date, and end_date parameters are the same as in the regular DATEDIFF function. The only difference is that DATEDIFF_BIG returns the result as a bigint.
For example:
SELECT DATEDIFF_BIG(day, '2000-01-01', '2150-01-01') AS DateDifferenceBig;
This would return the last difference between two date, in days between January 1, 2000, and January 1, 2150, as a bigint value, which can handle a much larger range of date differences than the regular DATEDIFF function.