# Date Maths – SQL Server

Please refer to the other articles in this series on DB2 and Oracle.

Rather than providing a list of all possible calculations I’ve focused on just listing the calculations that I’ve found occur often in a BI reporting or data analysis solution. I haven’t gone into details behind the calculations as this article should be treated as more of a reference than a tutorial and there are plenty of other articles available on the internet that explain in detail how to perform date calculations.

#### SQL Server Date Functions

Please refer to Microsoft’s Online Reference for Date and Time Functions for SQL Server for further details on the functions refered to below.

SQL Server 2008 Reference

SQL Server 2005 Reference

SQL Server 2000 Reference

System Date

`  GETDATE()`

Note, this will return the date and time according to the servers timezone. Use getUTCDate() to return the date and time in universal time coordinates.

To only return the date without the time use,

`  CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)`

Current Week

`  DATEPART(ww, GETDATE())`

Current Month as number,

`  MONTH(GETDATE())`
`  DATEPART(mm, GETDATE())`

Current Year

`  YEAR(GETDATE())`
`  DATEPART(yy, GETDATE())`

To get a string that representing the month of the current date use,

`  DATENAME(MM, GETDATE())`

To get a string that representing the day of the week of the current date use,

`  DATENAME(DD, GETDATE())`

To add 10 days to the current date use,

`  DATEADD(DD, 10, GETDATE())`

To add 2 weeks to the current date,

`  DATEADD(WW, 2, GETDATE())`

To determine the number of days between two dates use,

`  DATEDIFF(DD, STARTDATE, ENDDATE)`

To determine the number of weeks between two dates use,

`  DATEDIFF(WW, STARTDATE, ENDDATE)`

The above functions dateadd, datediff, datepart and datename use an argument to indicate what part of the date (day, week, month etc) we’re dealing with. Refer to the Microsoft’s SQL Server documentation for a full list.

#### SQL Server Date Calculations

First day of current week

`  dateadd(wk, datediff(wk, 0, getdate()), 0)`

Last day of current week

`  dateadd(wk, datediff(wk, 0, getdate()), 6)`

First day of last week

`  dateadd(wk, datediff(wk, 7, getdate()), 0)`

Last day of last week

`  dateadd(wk, datediff(wk, 7, getdate()), 6)`

First day of next week

`  dateadd(wk, datediff(wk, 0, getdate())+1, 0)`

Last day of next week

`  dateadd(wk, datediff(wk, 0, getdate())+1, 6)`

First day of current month

`  dateadd(mm, datediff(mm, 0, getdate()), 0)`

Last day of current month

`  dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0))`

First day of next month

`  dateadd(mm,datediff(mm,0,getdate())+1,0)`

Last day of next month

`  dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+2,0))`

First day of current year

`  dateadd(yy, datediff(yy, 0, getdate()), 0)`

Last day of current year

`  dateadd(ms,-3,dateadd(yy,datediff(yy,0,getdate())+1,0))`

First day of next year

`  dateadd(yy,datediff(yy,0,getdate())+1,0)`

Last day of next year

`  dateadd(ms,-3,dateadd(yy,datediff(yy,0,getdate())+2,0))`

The above formula will all use Sunday as the first day of the week by default. To use Monday as first day of the week alter the value of the last digit to use another day,

`  dateadd(wk,datediff(wk,0,getdate()),5)`

First Monday of the month

`  dateadd(wk,datediff(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())),0)`

There are several articles that can be found on the net that discuss the above formula, I got most of these examples above from the excellent article by Gregory A. Larsen

## 4 thoughts on “Date Maths – SQL Server”

1. Chris says:

I was wondering if you could explain how the First day of last week example works, breaking this down the datediff(wk, 7, getdate()) section returns the value 5782, I was just wondering how returning this result and using within the select dateadd(wk, datediff(wk, 7, getdate()), 0) statement actually works as I always thought the syntax required only dates. What is the significance of 5782?

Many thanks

Like

2. agulland says:

Hi, well the first thing to note is that the 7 and 0 in the formula are being treated as dates.

The datediff formula calculates the number of periods between two dates. Here we calculate the difference in weeks between the today (getdate) and the 7th day since start of the calendar. F=or sql server the start of the calendar is, i think, 1 Jan 1900 and so number of weeks between 7 Jan 1900 and today is 5782. Note this is one less than number of weeks between 1st day in calendar and today.

We then add this number of weeks to the first day of the calendar with dateadd(wk, 5782, 0) so 5782 weeks since 0 day will be first day of last week.

As you probably realise all the other formula are variations on this theme.

AL

Like

3. Chris says:

Thats extremely helpful, many thanks for explaining this! The main reason I needed to understand this process is that I need to translate this same situation in VB.Net. Unfortunately I don’t think it will be as simple as the SQL method.

Thanks again.

Like