Fiscal Quarter start and end dates based on GETDATE()

Question

Can anyone help me construct MS SQL formulas that return the current fiscal quarter, if the quarters go like this:

• December-February
• March-May
• June-August
• September-November

I've already come across this article, but it's for "regular" quarters.

Thank you!

Answer 1

With your bulleted list of quarters example, I'm assuming 12/01/yyyy as the beginning of the fiscal period. The best way to get fiscal year, fiscal quarter, etc., tied to a date (ie. GETDATE()) is to use a date dimension table. There are many examples of creating a date dimension table out on the internet, but most people seem to want one based on a company's (custom) fiscal year start and end period.

Below is a script that will quickly create a date dimension table (temp table for demo purposes) that can be used to lookup fiscal date values against date value coming from, for example: GETDATE(). The date dim table is based on your own fiscal start month and day, using any arbitrary year you choose to begin with... the script uses the date 12/01/2017 to keep in line with your quarters example for this year (currently 2017) to begin building the table.

Just pop in your start date value, based on your start fiscal month, into the variable @MyFiscalDateStart, and you're off and running with date dimension table containing the correct fiscal year and fiscal quarter number for any given calendar date.

IF OBJECT_ID('tempdb..#DimDate') IS NOT NULL
   DROP TABLE #DimDate;

DECLARE
   @MyFiscalDateStart  DATE = '20171201'
 , @MyFiscalMonthStart INT
 , @OffSet             INT;
SET @MyFiscalMonthStart = MONTH(@MyFiscalDateStart);

--SELECT
--   @MyFiscalDateStart
-- , @MyFiscalMonthStart;

SET @OffSet = @MyFiscalMonthStart - 1;
--SELECT
--   @OffSet

;
WITH CTE_DatesTable
AS (
   SELECT
      MyDate = @MyFiscalDateStart
   UNION ALL
   SELECT
      DATEADD(DAY, 1, MyDate)
   FROM
      CTE_DatesTable
   WHERE DATEADD(DAY, 1, MyDate) < DATEADD(YEAR, 5, @MyFiscalDateStart) -- goes 5 years out, can change number part to suit your needs
   )
SELECT
   DateKey           = MyDate
 , CalMonthNumber    = DATEPART(MONTH, MyDate)
 , FiscalMonthNumber = CASE
                          WHEN DATEPART(MONTH, MyDate) - @OffSet < 0
                          THEN (DATEPART(MONTH, MyDate) - @OffSet) + 12
                          WHEN DATEPART(MONTH, MyDate) - @OffSet = 0
                          THEN 12
                          ELSE DATEPART(MONTH, MyDate) - @OffSet
                       END
 , MonthLongName     = DATENAME(MONTH, MyDate)
 , MonthShortName    = SUBSTRING(LTRIM(DATENAME(MONTH, MyDate)), 0, 4)
 , CalendarYear      = DATEPART(YEAR, MyDate)
 , CalQtrNumber      = DATENAME(QUARTER, MyDate)
 , FYQtrNumber       = CASE
                          WHEN MyDate >= DATEADD(MONTH
                                               , -12
                                               , DATEADD(  MONTH
                                                         , 13 - CASE
                                                                   WHEN DATEPART(MONTH, MyDate) - @OffSet < 0
                                                                   THEN (DATEPART(MONTH, MyDate) - @OffSet) + 12
                                                                   WHEN DATEPART(MONTH, MyDate) - @OffSet = 0
                                                                   THEN 12
                                                                   ELSE DATEPART(MONTH, MyDate) - @OffSet
                                                                END
                                                         , DATEADD(DAY, 1, EOMONTH(MyDate, -1))
                                                        )
                                                )

                               AND MyDate < DATEADD(MONTH
                                                  , 3
                                                  , DATEADD(MONTH
                                                          , -12
                                                          , DATEADD(  MONTH
                                                                    , 13 - CASE
                                                                              WHEN DATEPART(MONTH, MyDate) - @OffSet < 0
                                                                              THEN (DATEPART(MONTH, MyDate) - @OffSet) + 12
                                                                              WHEN DATEPART(MONTH, MyDate) - @OffSet = 0
                                                                              THEN 12
                                                                              ELSE DATEPART(MONTH, MyDate) - @OffSet
                                                                           END
                                                                    , DATEADD(DAY, 1, EOMONTH(MyDate, -1))
                                                                   )
                                                           )
                                                   )
                          THEN 1
                          WHEN MyDate >= DATEADD(MONTH
                                               , 3
                                               , DATEADD(MONTH
                                                       , -12
                                                       , DATEADD(  MONTH
                                                                 , 13 - CASE
                                                                           WHEN DATEPART(MONTH, MyDate) - @OffSet < 0
                                                                           THEN (DATEPART(MONTH, MyDate) - @OffSet) + 12
                                                                           WHEN DATEPART(MONTH, MyDate) - @OffSet = 0
                                                                           THEN 12
                                                                           ELSE DATEPART(MONTH, MyDate) - @OffSet
                                                                        END
                                                                 , DATEADD(DAY, 1, EOMONTH(MyDate, -1))
                                                                )
                                                        )
                                                )

                               AND MyDate < DATEADD(MONTH
                                                  , 6
                                                  , DATEADD(MONTH
                                                          , -12
                                                          , DATEADD(  MONTH
                                                                    , 13 - CASE
                                                                              WHEN DATEPART(MONTH, MyDate) - @OffSet < 0
                                                                              THEN (DATEPART(MONTH, MyDate) - @OffSet) + 12
                                                                              WHEN DATEPART(MONTH, MyDate) - @OffSet = 0
                                                                              THEN 12
                                                                              ELSE DATEPART(MONTH, MyDate) - @OffSet
                                                                           END
                                                                    , DATEADD(DAY, 1, EOMONTH(MyDate, -1))
                                                                   )
                                                           )
                                                   )
                          THEN 2
                          WHEN MyDate >= DATEADD(MONTH
                                               , 6
                                               , DATEADD(MONTH
                                                       , -12
                                                       , DATEADD(  MONTH
                                                                 , 13 - CASE
                                                                           WHEN DATEPART(MONTH, MyDate) - @OffSet < 0
                                                                           THEN (DATEPART(MONTH, MyDate) - @OffSet) + 12
                                                                           WHEN DATEPART(MONTH, MyDate) - @OffSet = 0
                                                                           THEN 12
                                                                           ELSE DATEPART(MONTH, MyDate) - @OffSet
                                                                        END
                                                                 , DATEADD(DAY, 1, EOMONTH(MyDate, -1))
                                                                )
                                                        )
                                                )

                               AND MyDate < DATEADD(MONTH
                                                  , 9
                                                  , DATEADD(MONTH
                                                          , -12
                                                          , DATEADD(  MONTH
                                                                    , 13 - CASE
                                                                              WHEN DATEPART(MONTH, MyDate) - @OffSet < 0
                                                                              THEN (DATEPART(MONTH, MyDate) - @OffSet) + 12
                                                                              WHEN DATEPART(MONTH, MyDate) - @OffSet = 0
                                                                              THEN 12
                                                                              ELSE DATEPART(MONTH, MyDate) - @OffSet
                                                                           END
                                                                    , DATEADD(DAY, 1, EOMONTH(MyDate, -1))
                                                                   )
                                                           )
                                                   )
                          THEN 3
                          WHEN MyDate >= DATEADD(MONTH
                                               , 9
                                               , DATEADD(MONTH
                                                       , -12
                                                       , DATEADD(  MONTH
                                                                 , 13 - CASE
                                                                           WHEN DATEPART(MONTH, MyDate) - @OffSet < 0
                                                                           THEN (DATEPART(MONTH, MyDate) - @OffSet) + 12
                                                                           WHEN DATEPART(MONTH, MyDate) - @OffSet = 0
                                                                           THEN 12
                                                                           ELSE DATEPART(MONTH, MyDate) - @OffSet
                                                                        END
                                                                 , DATEADD(DAY, 1, EOMONTH(MyDate, -1))
                                                                )
                                                        )
                                                )

                               AND MyDate < DATEADD(MONTH
                                                  , 12
                                                  , DATEADD(MONTH
                                                          , -12
                                                          , DATEADD(  MONTH
                                                                    , 13 - CASE
                                                                              WHEN DATEPART(MONTH, MyDate) - @OffSet < 0
                                                                              THEN (DATEPART(MONTH, MyDate) - @OffSet) + 12
                                                                              WHEN DATEPART(MONTH, MyDate) - @OffSet = 0
                                                                              THEN 12
                                                                              ELSE DATEPART(MONTH, MyDate) - @OffSet
                                                                           END
                                                                    , DATEADD(DAY, 1, EOMONTH(MyDate, -1))
                                                                   )
                                                           )
                                                   )
                          THEN 4
                          ELSE NULL
                       END
 , FirstDayOfMonth   = DATEADD(DAY, 1, EOMONTH(MyDate, -1))
 , FYStartDate       = DATEADD(MONTH
                             , -12
                             , DATEADD(  MONTH
                                       , 13 - CASE
                                                 WHEN DATEPART(MONTH, MyDate) - @OffSet < 0
                                                 THEN (DATEPART(MONTH, MyDate) - @OffSet) + 12
                                                 WHEN DATEPART(MONTH, MyDate) - @OffSet = 0
                                                 THEN 12
                                                 ELSE DATEPART(MONTH, MyDate) - @OffSet
                                              END
                                       , DATEADD(DAY, 1, EOMONTH(MyDate, -1))
                                      )
                              )
 , FYEndDate         = EOMONTH(DATEADD(  MONTH
                                       , 12 - CASE
                                                 WHEN DATEPART(MONTH, MyDate) - @OffSet < 0
                                                 THEN (DATEPART(MONTH, MyDate) - @OffSet) + 12
                                                 WHEN DATEPART(MONTH, MyDate) - @OffSet = 0
                                                 THEN 12
                                                 ELSE DATEPART(MONTH, MyDate) - @OffSet
                                              END
                                       , DATEADD(DAY, 1, EOMONTH(MyDate, -1))
                                      )
                              )
 , FiscalYear        = YEAR(EOMONTH(DATEADD(  MONTH
                                            , 12 - CASE
                                                      WHEN DATEPART(MONTH, MyDate) - @OffSet < 0
                                                      THEN (DATEPART(MONTH, MyDate) - @OffSet) + 12
                                                      WHEN DATEPART(MONTH, MyDate) - @OffSet = 0
                                                      THEN 12
                                                      ELSE DATEPART(MONTH, MyDate) - @OffSet
                                                   END
                                            , DATEADD(DAY, 1, EOMONTH(MyDate, -1))
                                           )
                                   )
                           )
INTO
   #DimDate
FROM
   CTE_DatesTable

OPTION (MAXRECURSION 0);

SELECT
   dd.DateKey
 , dd.CalendarYear
 , dd.CalMonthNumber
 , dd.CalQtrNumber
 , dd.FiscalYear
 , dd.FiscalMonthNumber
 , dd.FYQtrNumber
 , dd.FirstDayOfMonth
 , dd.FYStartDate
 , dd.FYEndDate
FROM
   #DimDate AS dd;

Here is a screen shot of the results:

I hope this helps you. You can also modify this script to include day name such as Sunday, Monday, etc, and add in all other types of date associated columns such as holiday flags, etc.