Calculate quarters and year between two dates

Question

Post reply

Calculate quarters and year between two dates

ams00601

SSC-Addicted

Points: 473
More actions
January 27, 2015 at 4:48 am

#314068

hi all,
I have two dates....startdate = 01/06/2011 and enddate = 31/12/2013
I need to list out all the quarters and years between these two dates (including the start and end dates)
so output should be:
Q2 2011 (01/06/2011 falls in Q2 of 2011)
Q3 2011
Q4 2011
Q1 2012
Q2 2012
Q3 2012
Q4 2012
Q1 2013
Q2 2013
Q3 2013
Q4 2013 (31/12/2013 falls in Q4 2013)
Any help will be appreciated.
many thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

Instead of calculating them, try this table,

http://www.sqlservercentral.com/scripts/Date/68389/

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

-- You may need to extend the range of the virtual tally table.

SELECT [QuarterName] = 'Q' + DATENAME(qq,DATEADD(QQ,n,startdate)) + ' ' + CAST(YEAR(DATEADD(QQ,n,startdate)) AS VARCHAR(4))

FROM (SELECT startdate = '01/JUN/2011', enddate = '31/DEC/2013') d

CROSS APPLY (

SELECT TOP(1+DATEDIFF(QQ,startdate,enddate)) n

FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) rc(n)

) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Michael L John (1/27/2015)

Instead of calculating them, try this table,

http://www.sqlservercentral.com/scripts/Date/68389/%5B/quote%5D

What would really be interesting is to find out is, out of all the columns that people put into their calendar tables, how many they actually ever use and why they left out some of the more useful columns. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)