Date Maths – SQL Server

This article is the first in a series that lists date functions and date manipulation calculations for leading database systems and this article looks at the first of those – Microsoft 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

This article was published on March 17, 2009 by Al Gulland.

3 comments on “Date Maths – SQL Server

  1. Chris

    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

  2. agulland Post author

    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

  3. Chris

    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.

Leave a Reply