Examples of how to Calculate Different SQL Server Dates

文章目錄

• First Day of the Quarter
• Last Day of Prior Year
• Last Day of Current Month
• First Monday of the Month

Every now and then, you need to take the current date and calculate some other date. For instance, you might have an application that needs to determine what date is the first day of the month, or need to know the last day of the month. Now most of you probably already know how to separate the date into its piece (year, month, day, etc.) and use those pieces along with a number of functions to calculate a date that you might need. In this article, I will be showing how to use just the DATEADD and DATEDIFF function to calculate a number of different dates you might need to use in your applications.

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.

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

But now if you want to change this example to calculate a different first day of the week like “First Tuesday of the Week” you can change the set command above to “set DATEFIRST 2”.

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)

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 6th day of the current month. Using the 6th 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.