Get first day of quarter based on quarter

Question

Get first day of quarter based on quarter

curious_sqldba

SSC-Dedicated

Points: 36502
More actions
May 2, 2017 at 9:31 pm

#323272

I am trying to find a good way to calculate first day of quarter, for example I would like to know what would be first day of quarter 5 quarters from today

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

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

Thom A SSC Guru Points: 99143 More actions · Answer 1

Fairly simple this one.
DECLARE @Quarters int = 0; SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,GETDATE()) + @Quarters,0);
The value of Quarters can be changed to be a positive or negative integer. For example, 5 would give the value of the start of the quarter in 5 quarters time (2018-07-01).

If you don't understand what the logic is doing, feel free to ask.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Ant-Green SSC Guru Points: 113563 More actions · Answer 2

You might be best looking at a calendar table, much like the one from Sean here, then doing your date calculation against that table. Will then work for multiple variations, days, weeks, months, quarters, years etc

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 3

For fun
😎

USE TEEST; GO SET NOCOUNT ON;

DECLARE @NUM_QUARTERS INT = 7; ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N)) , NUMS(N) AS (SELECT TOP(@NUM_QUARTERS) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N FROM T T1,T T2) SELECT NM.N ,CEILING(MONTH(GETDATE()) / 3.0) AS CURRENT_QUARTER_01 ,DATEPART(QUARTER,GETDATE()) AS CURRENT_QUARTER_02 ,DATEFROMPARTS((YEAR(GETDATE()) + FLOOR(((NM.N * 3) - 2) / 12)) ,((NM.N * 3) - 2) % 12 ,1) AS QRT_FROM_BEGINNING_OF_YEAR_01 ,DATEADD(QUARTER,NM.N,DATEFROMPARTS(YEAR(GETDATE()),1,1)) AS QRT_FROM_BEGINNING_OF_YEAR_02 ,DATEADD(QUARTER,DATEPART(QUARTER,GETDATE()) ,DATEADD(QUARTER ,NM.N ,DATEFROMPARTS(YEAR(GETDATE()),1,1))) AS QRT_FROM_NEXT_QRT FROM NUMS NM;

Output
N CURRENT_QUARTER_01 CURRENT_QUARTER_02 QRT_FROM_BEGINNING_OF_YEAR_01 QRT_FROM_BEGINNING_OF_YEAR_02 QRT_FROM_NEXT_QRT -- ------------------- ------------------ ----------------------------- ----------------------------- ----------------- 1 2 2 2017-01-01 2017-04-01 2017-10-01 2 2 2 2017-04-01 2017-07-01 2018-01-01 3 2 2 2017-07-01 2017-10-01 2018-04-01 4 2 2 2017-10-01 2018-01-01 2018-07-01 5 2 2 2018-01-01 2018-04-01 2018-10-01 6 2 2 2018-04-01 2018-07-01 2019-01-01 7 2 2 2018-07-01 2018-10-01 2019-04-01