In order to understand these examples, let's first review the DATEDIFF and DATEADD functions. The DATEDIFF function calculates the amount of time between two dates, where the time part is based on an interval of time, such as hours, days, weeks, months, years, etc. The DATEADD function calculates a date by taking an interval of time, and adding it to a date, where the interval of time will be the same as those used by the DATEDIFF function. To find out more about the DATEDIFF and DATEADD functions, and the different intervals of time read Microsoft Books Online.

Using the DATEADD and DATEDIFF functions to calculated dates requires you to think a little differently about what it takes to convert the current date into a date you need. You must think in terms of date intervals. Such as, how many date intervals it is from the current date to the date you want to calculate. Or how many date intervals is it from today to some other date like '1900-01-01', and so on. Understanding how to look at date intervals will help you more easily understand my different date examples.

First Day of Month

For the first example, let me show you how to get the first day of the month from the current date. Remember now, this example and all the other examples in this article will only be using the DATEADD and DATEDIFF functions to calculate our desired date. Each example will do this by calculating date intervals from the current date, and then adding or subtracting intervals to arrive at the desired calculated date. Here is the code to calculate the first day of the month:

select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

Let me review how this works, by breaking this statement apart. The inner most function call "getdate()", as most of you probably already know, returns the current date and time. Now the next executed function call "DATEDIFF(mm,0,getdate())" calculates the number of months between the current date and the date "1900-01-01 00:00:00.000". Remember date and time variables are stored as the number of milliseconds since "1900-01-01 00:00:00.000"; this is why you can specify the first datetime expression of the DATEDIFF function as "0." Now the last function call, DATEADD, adds the number of months between the current date and '1900-01-01". By adding the number of months between our pre-determined date '1900-01-01' and the current date, I am able to arrive at the first day of the current month. In addition, the time portion of the calculated date will be "00:00:00.000."

The technique shown here for calculating a date interval between the current date and the year "1900-01-01," and then adding the calculated number of interval to "1900-01-01," to calculate a specific date, can be used to calculate many different dates. The next four examples use this same technique to generate different dates based on the current date.

Monday of the Current Week

Here I use the week interval (wk) to calculate what date is Monday of the current week. This example assumes Sunday is the first day of the week.

select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

If you don't want Sunday to be the first day of the week, then you will need to use a different method. Here is a method that David O Malley showed me that uses the DATEFIRST setting to set the first day of the week. This example sets Monday as the first day of the week, but by changing the DATEFIRST setting any day of the week could be the first day of the week.

set DATEFIRST 1

select DATEADD(dd, 1 - DATEPART(dw, getdate()), getdate())

First Day of the Year

Now I use the year interval (yy) to display the first day of the year.

select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

First Day of the Quarter

If you need to calculate the first day of the current quarter then here is an example of how to do that.

select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

Midnight for the Current Day

Ever need to truncate the time portion for the datetime value returned from the getdate() function, so it reflects the current date at midnight? If so then here is an example that uses the DATEADD and DATEDIFF functions to get the midnight timestamp.

select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

yesterday SELECT     DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), - 1) 

Expanding on the DATEADD and DATEDIFF Calculation

As you can see, by using this simple DATEADD and DATEDIFF calculation you can come up with many different dates that might be valuable.

All of the examples so far only calculated the current number of date intervals between the current date and "1900-01-01," and then added the number of intervals to "1900-01-01" to arrive at the calculated date. Say you modify the number of intervals to be added, or added additional DATEADD functions that used different time intervals, or subtracted intervals instead of adding intervals; by making these minor changes you can come up with many different dates.

Here are four examples that add an additional DATEADD function to calculate the last day dates for both the current and prior intervals.

Last Day of Prior Month

Here is an example that calculates the last day of the prior month. It does this by subtracting 3 milliseconds from the first day of the month example. Now remember the time portion in SQL Server is only accurate to 3 milliseconds. This is why I needed to subtract 3 milliseconds to arrive at my desired date and time.

select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  ), 0))

The time portion of the calculated date contains a time that reflects the last millisecond of the day ("23:59:59.997") that SQL Server can store.

Last Day of Prior Year

Like the prior example to get the last date of the prior year you need to subtract 3 milliseconds from the first day of year.

select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  ), 0))

Last Day of Current Month

Now to get the last day of the current month I need to modify slightly the query that returns the last day of the prior month. The modification needs to add one to the number of intervals return by DATEDIFF when comparing the current date with "1900-01-01." By adding 1 month, I am calculating the first day of next month and then subtraction 3 milliseconds, which allows me to arrive at the last day of the current month. Here is the TSQL to calculate the last day of the current month.

select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0))

Last Day of Current Year

You should be getting the hang of this by now. Here is the code to calculate the last day of the current year.

select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  )+1, 0))

First Monday of the Month

Ok, I am down to my last example. Here I am going to calculate the first Monday of the current month. Here is the code for that calculation.

select DATEADD(wk, DATEDIFF(wk,0,
            dateadd(dd,6-datepart(day,getdate()),getdate())
			                            ), 0)      

In this example, I took the code for "Monday of the Current Week," and modified it slightly. The modification was to change the "getdate()" portion of the code to calculate the 6^th day of the current month. Using the 6^th day of the month instead of the current date in the formula allows this calculation to return the first Monday of the current month.

Conclusion

I hope that these examples have given you some ideas on how to use the DATEADD and DATEDIFF functions to calculate dates. When using this date interval math method of calculating dates I have found it valuable to have a calendar available to visualize the intervals between two different dates. Remember this is only one way to accomplish these date calculations. Keep in mind there are most likely a number of other methods to perform the same calculations. If you know of another way, great, although if you do not, I hope these examples have given you some ideas of how to use DATEADD and DATEDIFF to calculate dates your applications might need.

» See All Articles by Columnist Gregory A. Larsen

May 7, 2003
Working with SQL Server Date/Time Variables: Part Two - Displaying Dates and Times in Different Formats
By Gregory A. Larsen

In my first article in this series, I discussed different aspects of entering date/time data into SQL Server DATETIME and SMALLDATE columns. This article will expand my discussion of date/time data by exploring how to use different SQL Server functions to display dates and times in different formats.

	CONVERT ( data_type [ ( length) ] , expression [ , style ] )  

PRINT '1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' + 
CONVERT(CHAR(19),GETDATE())  
PRINT '2) HERE IS MM-DD-YY FORMAT ==>' + 
CONVERT(CHAR(8),GETDATE(),10)  
PRINT '3) HERE IS MM-DD-YYYY FORMAT ==>' + 
CONVERT(CHAR(10),GETDATE(),110) 
PRINT '4) HERE IS DD MON YYYY FORMAT ==>' + 
CONVERT(CHAR(11),GETDATE(),106)
PRINT '5) HERE IS DD MON YY FORMAT ==>' + 
CONVERT(CHAR(9),GETDATE(),6) 
PRINT '6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>' + 
CONVERT(CHAR(24),GETDATE(),113)

1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>Feb  5 2003  5:54AM
2) HERE IS MM-DD-YY FORMAT ==>02-05-03
3) HERE IS MM-DD-YYYY FORMAT ==>02-05-2003
4) HERE IS DD MON YYYY FORMAT ==>05 Feb 2003
5) HERE IS DD MON YY FORMAT ==>05 Feb 03
6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>05 Feb 2003 05:54:39:567

	DATEPART(datepart, date)>

DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT CONVERT(CHAR(11),@DT,111) + 
SUBSTRING(CONVERT(CHAR(19),@DT,100),13,19)

DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT CAST(DATEPART(YYYY,@DT) AS CHAR(4)) + '/' 
     + RIGHT(CAST(100+DATEPART(MM,@DT) AS CHAR(3)),2) + '/'
     + RIGHT(CAST(100+DATEPART(DD,@DT) AS CHAR(3)),2) + ' '
     + CASE WHEN DATEPART(HH,@DT) < 13 
            THEN RIGHT(CAST(100+DATEPART(HH,@DT) AS CHAR(3)),2) 
            ELSE CAST(DATEPART(HH,@DT)-12 AS CHAR(2)) 
            END + ':'
     + RIGHT(CAST(100+DATEPART(MI,@DT) AS CHAR(3)),2)     
     + CASE WHEN DATEPART(HH,@DT) < 13
            THEN 'AM'
            ELSE 'PM'
            END

DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT REPLACE(CONVERT(CHAR(16),@DT,120),'-','/')

DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT CONVERT(CHAR(10),@DT,105)

	DATENAME( datepart , date )

DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT DATENAME(DAY,@DT) + ' ' + DATENAME(MONTH,@DT) + ' ' + DATENAME(YEAR, @DT)

DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT CONVERT(CHAR(19),@DT,100)

DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT DATENAME(MONTH,@DT) + ' ' + DATENAME(DAY,@DT) + ', ' + DATENAME(YEAR, @DT)

DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT '0' + CAST(MONTH(@DT) AS CHAR(1)) + '/' + CAST(DAY(@DT) AS 
CHAR(2)) + '/' + CAST(YEAR(@DT) AS CHAR(4))