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