LearnNowOnline

Blog

T-SQL: DATEADD and DATEDIFF Functions

Excerpt by Don Kiely | April 11, 2013

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;

To 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;

The 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);

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