How to use DATEDIFF function in SQL Server?

In MS SQL Server, the function DATEDIFF is used to calculate the time interval between two date values and return it as an integer.

General syntax for DATEDIFF:

DATEDIFF(datepart, start_date, end_date)

datepart is the unit of the interval to return. datepart can only be one of the following values, otherwise, SQL Server will not accept user-defined variables:

year, yyyy, yy = Year
quarter, qq, q = Quarter
month, mm, m = month
dayofyear = Day of the year
day, dy, y = Day
week, ww, wk = Week
weekday, dw, w = Weekday
hour, hh = hour
minute, mi, n = Minute
second, ss, s = Second
millisecond, ms = Millisecond

So basically you can count the difference in various units, from year to millisecond.

start_date and end_date are two date values. The value can be in one of these types:

Type	Format
date	YYYY-MM-DD
datetime	YYYY-MM-DD hh:mm:ss[.nnn]
datetimeoffset	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+/-]hh:mm
datetime2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]
smalldatetime	YYYY-MM-DD hh:mm:ss
time	hh:mm:ss[.nnnnnnn]

Example:

You want to calculate the delivery time, which is the difference between the order date and the receiving date, in days:

The syntax would be:

DATEDIFF(day, order_date, receiving_date)

The full demo SQL statement:

SELECT order_date, receiving_date,
DATEDIFF(day, order_date, receiving_date) AS delivery_time
FROM sales.orders
WHERE order_id = 9201;

Need a good GUI Tool for MS SQL Server? Try TablePlus, a modern, native tool for multiple databases including SQL Server, MySQL, PostgreSQL, SQLite, etc. And it’s free to use for as long as you need it to.