SQL Server DATEDIFF() Function

The DATEDIFF() function in SQL Server is used to calculate the time difference between two dates, and can return results in units such as years, months, days, hours, minutes, and seconds.

Syntax

DATEDIFF(datepart, startdate, enddate)

Parameters:

  • datepart: a string that represents the unit of time to calculate. Supported time units include year, quarter, month, day, week, hour, minute, second, millisecond.
  • startdate: the starting point of the time range.
  • enddate: the ending point of the time range.

Usage

The DATEDIFF() function is commonly used in many scenarios, such as:

  • calculating the number of days, hours, minutes, etc. between two dates.
  • calculating the time difference from a specific point in time to the current time.
  • calculating the time interval between two events.

Examples

Example 1

Calculating the number of days between two dates:

SELECT DATEDIFF(day, '2022-01-01', '2022-01-31');

Result:

30

Example 2

Calculating the number of minutes between a specific point in time and the current time:

SELECT DATEDIFF(minute, '2022-01-01 00:00:00', GETDATE());

Here, the GETDATE() function returns the current time, and the result is the difference in minutes between the current time and ‘2022-01-01 00:00:00’.

Conclusion

The DATEDIFF() function is a very useful function that can be used to calculate time differences in different units, making it very convenient in many scenarios.