SQL Server DateDiff Example

By:   |   Updated: 2011-10-10   |   Comments (11)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Dates


Problem

At the moment I have a project where I have to retrieve SQL Server data based on the difference between two dates. I read the tip on the DATEADD function and wanted to know about other functions available for date/time manipulation. Can you point me in the right direction?  Check out this tip to learn more about the SQL Server DateAdd function.

Solution

The most common function I use for date/time manipulation is the DATEDIFF function. Instead of adding or subtracting units of time from a specified date/time value, the DATEDIFF function retrieves the number of units of time between a start and end time. The DATEDIFF function can also be used in a WHERE clause as well as ORDER BY and HAVING clauses. The units of time available for the DATEDIFF are the same as those for the DATEADD function.

Examples of Using the SQL Server DATEADD Function

Below are examples of using the DATEDIFF function:

Unit of time Query Result
NANOSECOND SELECT DATEDIFF(NANOSECOND,'2011-09-23 17:15:22.5500000','2011-09-23 17:15:22.55432133') 4321300



MICROSECOND SELECT DATEDIFF(MICROSECOND,'2011-09-23 17:15:22.5500000','2011-09-23 17:15:22.55432133') 4321



MILLISECOND SELECT DATEDIFF(MILLISECOND,'2011-09-23 17:15:22.004','2011-09-23 17:15:22.548') 544


SECOND SELECT DATEDIFF(SECOND,'2011-09-23 17:15:30','2011-09-23 17:16:23') 53


MINUTE SELECT DATEDIFF(MINUTE,'2011-09-23 18:03:23','2011-09-23 17:15:30') -48


HOUR SELECT DATEDIFF(HH,'2011-09-23 18:03:23','2011-09-23 20:15:30') 2


WEEK SELECT DATEDIFF(WK,'09/23/2011 15:00:00','12/11/2011 14:00:00') 12


DAY SELECT DATEDIFF(DD,'09/23/2011 15:00:00','08/02/2011 14:00:00') -52


DAYOFYEAR SELECT DATEDIFF(DY,'01/01/2011 15:00:00','08/02/2011 14:00:00') 213


MONTH SELECT DATEDIFF(MM,'11/02/2011 15:00:00','01/01/2011 14:00:00') -10


QUARTER SELECT DATEDIFF(QQ,'01/02/2011 15:00:00','08/01/2011 14:00:00') 2


YEAR SELECT DATEDIFF(YY,'01/02/2011 15:00:00','01/01/2016 14:00:00') 5

One practical example of using the DATEDIFF function in SQL Server is in a WHERE clause by selecting all employees in the AdventureWorks2008R2 database whose date of hire was in March 2003. The application passes in two parameters: a string representing the number of the desired month (i.e. January=1, February=2, etc.) and the string representing the desired year. Then we use the DATEDIFF function with the MONTH unit of time:

DECLARE @Month VARCHAR(2), @Year CHAR(4)
SELECT @Month = '3', @Year = '2003'
DECLARE @Date DATE = @Month + '/1/' + @Year
SELECT LoginID, OrganizationNode, OrganizationLevel, JobTitle, BirthDate,
MaritalStatus, Gender, HireDate, SalariedFlag, VacationHours,
SickLeaveHours, CurrentFlag, rowguid, ModifiedDate
FROM AdventureWorks2008R2.HumanResources.Employee
WHERE DATEDIFF(MONTH,@Date,HireDate) = 0;
GO
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Cullen Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-10-10

Comments For This Article




Thursday, July 23, 2015 - 5:09:59 PM - fernando Back To Top (38283)

Buenas tardes tengo una duda de como obtener el ultimo dia del mes ya que mi tabla solamente tiene el mes y año y automaticamente genera el ultimo dis del mes mi query es el siguiente espero puedan ayudarme.

 

select 

cast(anio as varchar(4)) + cast(mes as varchar(2)) as cv_periodo,

cast(

   day(

   dateadd(month,+1,cast(anio as varchar(4)) + '/01/' +

          case

              when len(ltrim(rtrim(cast(mes as varchar(2)))))=1 then '0' + ltrim(rtrim(cast(mes as varchar(1))))

              else ltrim(rtrim(cast(mes as varchar(2))))

           end 

 ) ) 

     as varchar(2))

+ ' de ' +

ltrim(rtrim(descripcion_mes)) + ' de ' + cast(anio as varchar(4)) as periodo

from 

idc.dbo.TBL_MESES_ANIOS

order by 

anio desc, mes desc

Tuesday, June 25, 2013 - 1:06:50 PM - Steve Back To Top (25563)

While a WHILE loop can "find" the last day of the month, it's not going to perform well if you need to generate a rather large number of such dates.   This kind of thing is much easier using a "Tally Table" type of solution.    A Tally table is nothing but table of numbers (int) where the minimum value is 0 or 1, and the maximum value anywhere from 10,000 or so to as high as 1 BILLION, depending on your needs.   For date purposes, 10,000 is enough to encompass at least 30 years of dates, so that should work just fine for most scenarios.   Assuming that such a table exists and is named dbo.Tally, with a single INT field named N; consider the following:

 

DECLARE @START_DT AS date = '12/31/1959'

;WITH ALL_DATES AS (

SELECT DATEADD(dd, T.N, @START_DT)) AS DT_YEAR,

MONTH(DATEADD(dd, T.N, @START_DT)) AS DT_MONTH,

DATEADD(dd, T.N, @START_DT) AS THE_DATE

FROM dbo.Tally AS T

),

MONTH_END_DATES AS (

SELECT D.DT_YEAR, D.DT_MONTH, MAX(D.THE_DATE) AS ME_DATE

FROM ALL_DATES AS D

WHERE DATEPART(dw, D.THE_DATE) IN (2, 3, 4, 5, 6)

GROUP BY D.DT_YEAR, D.DT_MONTH

)

SELECT *

FROM YOUR_TABLE AS T

LEFT OUTER JOIN MONTH_END_DATES AS MED

ON T.YOUR_YEAR_FIELD = MED.DT_YEAR

AND T.YOUR_MONTH_FIELD = MED.DT_MONTH

 

This could easily be modified to fit many situations.   If you need help with creating a Tally table, please see www.sqlservercentral.com and search for a Tally table article by Jeff Moden.

 

Friday, January 18, 2013 - 4:53:41 PM - George Back To Top (21563)

I have saved the following as a reference for myself.  The example refers to date in days, but I also give myself the code for using month, quarter, week, and yea; you then need to replace the DD from each for whatever you need.  Using the base example, I can adjust it for the proper number.  (x numbers of days ago, etc.)

Type    Example         Result                                How
Day    Yesterday         2013-01-17 00:00:00.000    DATEADD(DD, DATEDIFF(DD, 0, GETDATE()) - 1, 0)
Day    YesterdayEnd    2013-01-17 23:59:59.997    DATEADD(MS, -3, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0))
Day    Today              2013-01-18 00:00:00.000    DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)
Day    TodayEnd         2013-01-18 23:59:59.997    DATEADD(MS, -3, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()) + 1, 0))
Day    Tomorrow         2013-01-19 00:00:00.000    DATEADD(DD, DATEDIFF(DD, 0, GETDATE()) + 1, 0)
Day    TomorrowEnd    2013-01-19 23:59:59.997    DATEADD(MS, -3, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()) + 2, 0))

DatePart    Code
Day           DD
Month        MM
Quarter      QQ
Week        WK
Year          YY

Monday, August 20, 2012 - 3:27:54 PM - Tim Cullen Back To Top (19133)

Steve:

 

If you want the last business day without having to worry about the weekends then you can do one of two things:

1.  Create a table with a bunch of dates and date information and then do a SELECT statement to find the last weekday of the month

2. Use this statement: DECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME, @TargetDate SMALLDATETIME

SELECT @StartDate = '08/01/2012'

SELECT @EndDate = DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate))

 

WHILE @TargetDate IS NULL

BEGIN

IF DATEPART(DW,@EndDate) BETWEEN 2 AND 6

BEGIN

SELECT @TargetDate = @EndDate

END

SELECT @EndDate = DATEADD(DAY,-1,@EndDate)

CONTINUE

END

SELECT @TargetDate

 

Coming up with a holidays table is a little more complicated but, again, most people will tell you to create a date table, mark the dates that are holidays, and then SELECT from that table.  Let me know if you want additional information

Monday, August 20, 2012 - 11:03:40 AM - Steve Back To Top (19127)

I know how to calculate the last day of any month. How would you calculate the last Business day of any month without worrying about Holidays?

thanks

Steve

Wednesday, June 27, 2012 - 4:57:26 PM - SQL Server Helper Back To Top (18234)

Here's a link that shows the different uses of the DATEDIFF date function:

http://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=15142666-895a-476c-a094-f78fa58c5cea&tid=57&tkey=uses-of-the-datediff-date-function

Tuesday, October 11, 2011 - 11:14:12 AM - Anna Back To Top (14830)

Thanks Usman, this clearly answers my question. It is very helpful.

Tuesday, October 11, 2011 - 1:52:45 AM - Usman Butt Back To Top (14827)

Hi Anna,

I should rather re-state that

Wrapping table columns of WHERE or JOIN clause inside of any type of function (system or user defined) can dramatically decrease query performance because this practice create hurdles in query optimizer work of proper index selection. But In my example the dateadd and datediff are used to just create the date range (no column of the table is used in the functions). I did use these functions in WHERE clause to keep the resemblance with the code and it would not affect the index hit. To make it clear we can re-write the code something like

Declare @StartOfMonth DATETIME, @EndOfMonth DATETIME

SET @StartOfMonth = DATEADD(mm, DATEDIFF(mm, 0, @Date), 0)

SET @EndOfMonth = DATEADD(ms, -3, DATEADD(mm, 1, @StartOfMonth))

And then the WHERE clause becomes

WHERE HireDate BETWEEN StartOfMonth AND @EndOfMonth

Hope this will clarify the situation.


And as far as index is concerned, yes, the index should be on hire date.


Moreover, you can also use a TALLY table OR Calendar table for such queries and may get better performance. Cheers.

Best Regards,
Usman Butt

 

 

Monday, October 10, 2011 - 4:57:26 PM - Anna Back To Top (14826)

Thanks for the tip.

A question when you say: It should rather be used only in SELECT clause. WHERE clause can be re-written something like

But I see the example still have dateAdd and dateDif in the where clause. So in the rewritten where clause, the index is the hiredate?

 

Thanks

Monday, October 10, 2011 - 4:06:38 PM - Tim Cullen Back To Top (14825)

Hey Usman:

Thanks for the comments.  When I originally executed the SQL statement the table count was small and there was relatively no difference in the performance.  However, I executed a query on a table with 17 million records and noticed a significant difference in the IO price (see statistics below).  You are correct in that using the BETWEEN clause refers to an index.  You also revealed a good point in that SQL statements can be executed more than once, and it is good to analyze each statement's performance and cost to make sure you either making good use of indexes or create an index.

 

Tim

 

DATEDIFF

(151208 row(s) affected)

Table 'tbl_WeatherObservations'. Scan count 5

, logical reads 174086, physical reads 510

, read-ahead reads 173341, lob logical reads 0

, lob physical reads 0, lob read-ahead reads 0.

 

(1 row(s) affected)

 

 SQL Server Execution Times:

   CPU time = 4585 ms,  elapsed time = 26785 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 

 

BETWEEN DATES

(151208 row(s) affected)

Table 'tbl_WeatherObservations'. Scan count 1

, logical reads 173624, physical reads 346

, read-ahead reads 173341, lob logical reads 0

, lob physical reads 0, lob read-ahead reads 0.

 

(1 row(s) affected)

 

 SQL Server Execution Times:

   CPU time = 2028 ms,  elapsed time = 25933 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

Monday, October 10, 2011 - 9:28:38 AM - Usman Butt Back To Top (14824)

Hi,

It was a nice article with a simple example and was very easy to understand the basic functionality. But one should not use functions in a WHERE clause (against the best practices). It should rather be used only in SELECT clause. WHERE clause can be re-written something like

WHERE HireDate BETWEEN DATEADD(mm, DATEDIFF(mm, 0, @Date), 0) -- START OF MONTH OF SEARCHED DATE
AND
DATEADD(ms,-3, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 1, @Date)), 0)) -- END OF MONTH OF SEARCHED DATE

in order to hit the index. Cheers.

 

Best Regards,
Usman Butt














get free sql tips
agree to terms