E Happy Learning

SQL Server Function #

Problem
Often, we need to calculate the difference between two dates and return the results in a desired date part or increment such as days, hours, minutes. Fortunately, SQL Server provides a function for this.

In this article I will demo the SQL functions DATEDIFF and DATEDIFF_BIG and share several examples of how to use each. I will also show you the limitations and how to work around them. You will also learn how to calculate how old the city of Delhi is in nanoseconds.

Solution
We will explore the DATEDIFF and DATEDIFF_BIG functions, show how they are used, and provide several examples.

What is SQL Server DATEDIFF Function
DATEDIFF() is a basic SQL Server function that can be used to do date math. Specifically, it gets the difference between 2 dates with the results returned in date units specified as years, months days, minutes, seconds as an int (integer) value.

Syntax: DATEDIFF( DatePart, StartDate, EndDate )

What is SQL Server DATEDIFF_BIG Function

DATEDIFF_BIG() is a SQL function that was introduced in SQL Server 2016. It can be used to do date math as well. Specifically, it gets the difference between 2 dates with the results returned in date units specified as years, months days, minutes, seconds as a bigint value.

Syntax: DATEDIFF_BIG( DatePart, StartDate, EndDate )

SELECT DATEDIFF( MILLISECOND, '07-04-2020', '07-05-2020') --> = 86400000

SELECT DATEDIFF_BIG( NANOSECOND, '07-04-2020', '07-05-2020') --> = 86400000000000

DatePart Name Abbreviation
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns

Example SQL Server DATEDIFF Code Output
How many minutes in a day? SELECT DATEDIFF(MINUTE, '07-04-2020', '07-05-2020') 1440 minutes
How many hours in a day? SELECT DATEDIFF(HOUR, '01-01-2020', '01-02-2020') 24 Hours
How many days in a year? SELECT DATEDIFF(DAY, '01-01-2020', '12-31-2020') 365 Days
How many months in a year? SELECT DATEDIFF(MONTH, '01-01-2019', '01-01-2020') 12 Months
How many years from 2000 to 2020? SELECT DATEDIFF(YEAR, '01-01-2000', '01-01-2020') 20 Years