Blog

T-SQL: DATEADD and DATEDIFF Functions

Excerpt by Don Kiely

Date and time functions perform operations on date and time values to return a string, numeric, or date/time value. Compared to the string manipulation functions in T-SQL, you have way more flexibility when working with dates and times.

Use DATEADD to perform computations on dates by adding an interval of time to the specified date. The following query adds 2 to the year, month, and day. The Figure below shows the results.

SELECT
DATEADD(yy, 2, GETDATE()) AS AddYear,
DATEADD(mm, 2, GETDATE()) AS AddMonth,
DATEADD(dd, 2, GETDATE()) AS AddDay;

Performing computations on dates with DATEADDTo subtract from a date, use DATEADD and pass in a negative number for the time interval.

Use DATEDIFF if you want to calculate the length of time between two date/time values. As with DATEADD, DATEDIFF allows you to specify the unit of measure. The following query uses the Northwind database to calculate the number of days that elapsed between various dates in the Northwind Orders table, to provide various metrics about company performance. The first date passed to DATEDIFF is subtracted from the second date. The Figure below shows a few rows of the result set.

SELECT
OrderDate, RequiredDate, ShippedDate,
DATEDIFF(dd, OrderDate, RequiredDate) AS LeadTime,
DATEDIFF(dd, OrderDate, ShippedDate) AS DaysToShip,
DATEDIFF(dd, ShippedDate, RequiredDate) AS DaysEarly
FROM dbo.Orders;

DATEDIFFThe following code uses a query that specifies a date and then calculates the last day of the month that the date is in. The logic is to add one month to the date and then subtract the number of days in the day portion of that date in the next month. This brings the calculation back to the last day of the original date's month. This calculates that 2016-02-29 is the last day of that month.

DECLARE @date datetime;
SET @date='2016-02-07';
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)),
DATEADD(m,1,@date)) AS LastDayOfMonth;

In SQL Server 2012, an even easier way to find the last day of the month is with the EOMONTH function, which takes a date and an optional parameter that lets you specify a number of months to add. The following code finds the last day of the month for the same date as the previous code, as well as the last day of the month five months later. Notice that in February the last day is the 29th but in July it is the 31st, as shown in the Figure below.

DECLARE @date datetime;
SET @date='2016-02-07';
SELECT EOMONTH(@date), EOMONTH(@date, 5);

EOMONTH

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL course written by expert Don Kiely.



Don Kiely
Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.


This course excerpt was originally posted April 11, 2013 from the online courseware SQL Server 2012, Part 7 of 9: Introduction to T-SQL by Don Kiely

SQL Server