Your browser (Internet Explorer 7 or lower) is out of date. It has known security flaws and may not display all features of this and other websites. Learn how to update your browser.

X

Navigate / search

How to Calculate Quarters in SQL Server

Not long ago, I found determining the start and end dates for each of the four quarters in the year to be a more difficult task than I had anticipated. I ran into even more difficulty because I was using SQL Server 2000. Use of the newest SQL functions would have made the query much easier, but I didn't have that luxury. The code below is what I came up with to calculate the quarters.

  1. SELECT [YEAR], [quarter], DATEADD(qq,[quarter]-1,DATEADD(yy,[YEAR]-YEAR(0),0)) [quarterstart],
  2.         DATEADD(ms,-3,DATEADD(qq,[quarter],DATEADD(yy,[YEAR]-YEAR(0),0))) [quarterend]
  3. FROM (SELECT DATEPART(yy,DATEADD(yy,n1.num+n2.num*10+DATEDIFF(yy,0,'01/01/2011'),0)) [YEAR], n3.num [quarter]
  4.         FROM (SELECT 0 [num] UNION all SELECT 1 UNION all SELECT 2 UNION all SELECT 3 UNION all SELECT 4 UNION all SELECT 5 UNION all SELECT 6
  5.                         UNION all SELECT 7 UNION all SELECT 8 UNION all SELECT 9) n1,
  6.                 (SELECT 0 [num] UNION all SELECT 1 UNION all SELECT 2 UNION all SELECT 3 UNION all SELECT 4 UNION all SELECT 5 UNION all SELECT 6
  7.                         UNION all SELECT 7 UNION all SELECT 8 UNION all SELECT 9) n2,
  8.                 (SELECT 1 [num] UNION all SELECT 2 UNION all SELECT 3 UNION all SELECT 4) n3
  9.         WHERE DATEPART(yy,DATEADD(yy,n1.num+n2.num*10+DATEDIFF(yy,0,'01/01/2011'),0)) < YEAR(GETDATE())
  10.                 or (DATEPART(yy,DATEADD(yy,n1.num+n2.num*10+DATEDIFF(yy,0,'01/01/2011'),0)) = YEAR(GETDATE())
  11.                         and n3.num <= DATEPART(qq,GETDATE()))
  12. ) t

The above SQL code may look confusing at first, but read it over a couple of times and I think it will make sense to you. Let's look at each section of the code and how it works.

  1. SELECT 0 [num] UNION all SELECT 1 UNION all SELECT 2 UNION all SELECT 3 UNION all SELECT 4 UNION all SELECT 5 UNION all SELECT 6
  2.                 UNION all SELECT 7 UNION all SELECT 8 UNION all SELECT 9) n1,
  3.         (SELECT 0 [num] UNION all SELECT 1 UNION all SELECT 2 UNION all SELECT 3 UNION all SELECT 4 UNION all SELECT 5 UNION all SELECT 6
  4.                 UNION all SELECT 7 UNION all SELECT 8 UNION all SELECT 9) n2,
  5.         (SELECT 1 [num] UNION all SELECT 2 UNION all SELECT 3 UNION all SELECT 4) n3

This SQL code is used to calculate the years for the next 99 years. Another select statement could be added to go for hundreds or thousands of years, if necessary. The last select query is used to calculate the quarters in each year. All these queries together effectively create a couple of cross joins. Using SQL Server 2005 or 2008, this can be done much easier using a CTE.

  1. WHERE DATEPART(yy,DATEADD(yy,n1.num+n2.num*10+DATEDIFF(yy,0,'01/01/2011'),0)) < YEAR(GETDATE())
  2.         or (DATEPART(yy,DATEADD(yy,n1.num+n2.num*10+DATEDIFF(yy,0,'01/01/2011'),0)) = YEAR(GETDATE())
  3.                 and n3.num <= DATEPART(qq,GETDATE()))

The where clause of the SQL query restricts the data set being returned. The first condition will return results for each quarter if the year is in the past. The second condition runs if the year is equal to the current year and returns current and previous quarters.

  1. SELECT [YEAR], [quarter], DATEADD(qq,[quarter]-1,DATEADD(yy,[YEAR]-YEAR(0),0)) [quarterstart],
  2.         DATEADD(ms,-3,DATEADD(qq,[quarter],DATEADD(yy,[YEAR]-YEAR(0),0))) [quarterend]

Here, the start and end dates of each quarter are calculated. The start date is determined by adding the current year to the SQL Server start date of January 1, 1900. From there, the appropriate number of quarters are added to the date to arrive at each quarter start date.

The end date is determined by the same calculation, except by looking ahead one quarter and subtracting three milliseconds to arrive at the exact date and time each quarter ends. Three milliseconds are subtracted because SQL Server stores times in increments of 3 milliseconds. If you were to subtract one millisecond, SQL Server would round to the same date and time you are trying to subtract from.