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.
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