Get Quarters StartDate and EndDate from Year

Question

I am using SQL Server 2008. I want to get Start Date and End Date of all quarters of year. For example if I pass 2013 in query then output should like

StartDate                 EndDate                   QuarterNo
-------------------------------------------------------------
2013-04-01 00:00:00.000   2013-06-30 00:00:00.000      1
2013-07-01 00:00:00.000   2013-09-30 00:00:00.000      2
2013-10-01 00:00:00.000   2013-12-31 00:00:00.000      3
2014-01-01 00:00:00.000   2014-03-31 00:00:00.000      4

Because of Financial Year start from 1st April, I want to get 1st quarter start from 1st April. How can I get this output? Thanks for help...

podiluska · Accepted Answer · 2013-09-17T08:04:02.983

select 
    dateadd(M, 3*number, CONVERT(date, CONVERT(varchar(5),@year)+'-1-1')),
    dateadd(D,-1,dateadd(M, 3*number+3, CONVERT(date, CONVERT(varchar(5),@year)+'-1-1'))),
    Number QuarterNo
from master..spt_values 
where type='p' 
and number between 1 and 4

You'll probably want to use dates, not datetimes, otherwise nothing during the day of the last day of the quarter is included in your quarter (eg: 2013-06-30 14:15)

To go the other way, use datepart

select ((DATEPART(q,@date)+2) % 4)+1

score 5 · Answer 2 · answered Jan 24 '14 at 16:39

My favorite way but EOMONTH and DATEFROMPARTS doesn't exist before SQLServer 2012:

DECLARE
    @FISCAL_YEAR INT = 2013,
    @QUATER INT = 2
SELECT 
    DATEFROMPARTS(@fiscal_year,(@Quater * 3)-2,1) AS QuaterStart,
    EOMONTH(DATEFROMPARTS(@fiscal_year,@Quater * 3,1)) AS QuaterEnd

score 3 · Answer 3 · answered Sep 17 '13 at 08:17

One more way to do it

SELECT DATEADD(mm, (quarter - 1) * 3, year_date) StartDate,
       DATEADD(dd, -1, DATEADD(mm, quarter * 3, year_date)) EndDate,
       quarter QuarterNo
  FROM
(
  SELECT '2013-04-01' year_date
) s CROSS JOIN 
(
  SELECT 1 quarter UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 UNION ALL
  SELECT 4
) q

Output:

| StartDate  |    EndDate | QuarterNo |
|------------|------------|-----------|
| 2013-04-01 | 2013-06-30 |         1 |
| 2013-07-01 | 2013-09-30 |         2 |
| 2013-10-01 | 2013-12-31 |         3 |
| 2014-01-01 | 2014-03-31 |         4 |

Here is SQLFiddle demo

score 1 · Answer 4 · answered Feb 16 '21 at 22:06

Stumbled upon these two posts when looking to truncate dates.

-- DATE TRUNCATE FORMAT
CAST(DATEADD(datepart, DATEDIFF(datepart, 0, date), 0) AS DATE)

Here's an example query I wrote that may help:

DECLARE @datetime DATETIME = GETDATE() -- '2021-08-21 16:48:09.018'

SELECT
  @datetime                                                                                       AS [Date Time]
, CAST(@datetime AS DATE)                                                                         AS [Date]
, DATEPART(QUARTER, @datetime)                                                                    AS [Quarter of Year]
, DATEDIFF(QUARTER, 0, @datetime)                                                                 AS [Quarter Integer]
, DATEDIFF(QUARTER, 0, @datetime) - 1                                                             AS [Previous Quarter Integer]
, DATEDIFF(QUARTER, 0, @datetime) + 1                                                             AS [Next Quarter Integer]
, CAST(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @datetime), 0) AS DATE)                              AS [Quarter Date 1]
, DATEFROMPARTS(YEAR(@datetime), (DATEPART(QUARTER, @datetime) * 3) - 2, 1)                       AS [Quarter Date 2]
, CAST(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @datetime) - 1, 0) AS DATE)                          AS [Previous Quarter Start Date 1]
, DATEADD(QUARTER, -1, DATEFROMPARTS(YEAR(@datetime), (DATEPART(QUARTER, @datetime) * 3) - 2, 1)) AS [Previous Quarter Start Date 2]
, CAST(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @datetime) + 1, 0) AS DATE)                          AS [Next Quarter Start Date 1]
, DATEADD(QUARTER, +1, DATEFROMPARTS(YEAR(@datetime), (DATEPART(QUARTER, @datetime) * 3) - 2, 1)) AS [Next Quarter Start Date 2]
, EOMONTH(DATEFROMPARTS(YEAR(@datetime), DATEPART(QUARTER, @datetime) * 3, 1))                    AS [Quarter End Date 1]
, CAST(DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @datetime) + 1, 0)) AS DATE)        AS [Quarter End Date 2]
, CAST(DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @datetime), 0)) AS DATE)            AS [Previous Quarter End Date]
, CAST(DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @datetime) + 2, 0)) AS DATE)        AS [Next Quarter End Date]

score 0 · Answer 5 · answered Sep 17 '13 at 08:07

 CREATE FUNCTION cal_quarter (@DATE SMALLDATETIME)

    RETURNS INT
    AS
BEGIN
declare @CalendarQuarter int
        IF month(@DATE) in(4,5,6)
            BEGIN
                set @CalendarQuarter =1
            END
        if month(@DATE) in(7,8,9)
            BEGIN
                set @CalendarQuarter =2
            END
        if month(@DATE) in(10,11,12)
            BEGIN
                set @CalendarQuarter =3
            END
        if month(@DATE) in(1,2,3)
            BEGIN
                set @CalendarQuarter =4
            END
return @CalendarQuarter
END

This is a function which returns the calender quarter.

You should send a DATE as a parameter.

Hope this helps !

score 0 · Answer 6 · edited Apr 16 '18 at 09:16

0

Last date of previous quarter. This code can run on SQL Server 2012 and later

select EOMONTH(DATEFROMPARTS(year(DATEADD(month, -1, GETDATE())),DATENAME(qq,DATEADD(month, -1, GETDATE())) * 3,1)) AS QuarterEnd

edited Apr 16 '18 at 09:16

answered Apr 16 '18 at 08:32

Mahabubuzzaman

349
3
3

3

Question is for SQL Server 2008 – Anuraag Baishya Apr 16 '18 at 08:50
@AnuraagBaishya Sure, but an update doesn't do any harm. Mahabubuzzaman would you mind formatting your code a bit better to prevent hor. scrolling and adding a brief explanation? – Gert Arnold Apr 16 '18 at 18:36
DATEFROMPARTS created a first date of mentioned month and EOMONTH return last date of said month . – Mahabubuzzaman Apr 19 '18 at 08:14

score 0 · Answer 7 · answered Nov 06 '21 at 05:22

Here's a solution for calendar quarters using a recursive CTE.

DECLARE @Date DATE = '2021-11-06'

;WITH CalendarQuarters
AS
(
    SELECT 1 AS QuarterNo
          ,CONVERT(DATE,DATEADD(DAY,-(DATEPART(DAY,@Date))+1, DATEADD(MONTH,-(DATEPART(MONTH,@Date))+1,@Date))) AS StartDate
          ,CONVERT(DATE,DATEADD(DAY,-(DATEPART(DAY,@Date)), DATEADD(MONTH,-(DATEPART(MONTH,@Date))+4,@Date))) AS EndDate
    UNION ALL
    SELECT 
           QuarterNo + 1
          ,CONVERT(DATE,DATEADD(DAY,-(DATEPART(DAY,@Date))+1, DATEADD(MONTH,-(DATEPART(MONTH,@Date))+1+QuarterNo*3,@Date))) AS StartDate
          ,CONVERT(DATE,DATEADD(DAY,-(DATEPART(DAY,@Date)), DATEADD(MONTH,-(DATEPART(MONTH,@Date))+4+QuarterNo*3,@Date))) AS EndDate
    FROM CalendarQuarters
    WHERE QuarterNo < 4
)

SELECT * FROM CalendarQuarters

score -1 · Answer 8 · edited Feb 27 '15 at 14:13

-1

SELECT 
    CAST(DATEADD(M,3*(NUMBER-1),DATEADD(YYYY,DATEDIFF(YYYY,0,GETDATE()),0)) AS DATE) [QT START DATE]
    , CAST(DATEADD(D,-1,DATEADD(M,3*(NUMBER),DATEADD(YYYY,DATEDIFF(YYYY,0,GETDATE()),0))) AS DATE) [QT END DATE]
    , NUMBER AS [QUARTER NAME]
FROM MASTER..SPT_VALUES 
WHERE TYPE='P' 
    AND NUMBER BETWEEN 1 AND 4

edited Feb 27 '15 at 14:13

Mohamad Shiralizadeh

8,329
6
58
93

answered Feb 27 '15 at 12:56

Govindaraj

1
1

Get Quarters StartDate and EndDate from Year

8 Answers8

Linked