3

As I have to calculate Start date of specific quarter and Quarter No from any financial year start date based on @firstMonthOfFiscalyear parameter. Let's say if @firstMonthOfFiscalyear =4 mean my financial year start date is 1 April and my quarter no start as below. Q1 - April to Jun Q2 - July to Sep Q3 - Oct to Dec Q4 - Jan to March

This quarter no will change based on @firstMonthOfFiscalyear parameter value.

From this I am able to get Quarter number but not able to get Start date of that quarter. So anyone can help me on this.

DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME
DECLARE @firstMonthOfFiscalyear int = 4     --Finanical year start month
SET @StartDateTime = '2017-04-01'
SET @EndDateTime = '2019-03-31';
WITH DateRange(Dates) AS 
(
SELECT @StartDateTime as Date
Union ALL
SELECT DATEADD(d,1,Dates)
FROM DateRange 
WHERE Dates < @EndDateTime
)
SELECT Dates
,FLOOR(((12 + MONTH(Dates) - @firstMonthOfFiscalyear) % 12) / 3 ) + 1   as quarterNo
    , DATEADD(month, (IIF((month(dates)-@firstMonthOfFiscalyear)<0,(month(dates)-@firstMonthOfFiscalyear)+12,(month(dates)-@firstMonthOfFiscalyear))/3)*3,  CAST( DATEFROMPARTS(year(dates),@firstMonthOfFiscalyear ,1) as Datetime)) as QuarterStartDate
FROM DateRange
OPTION (MAXRECURSION 0)
Nikunj
  • 31
  • 1
  • 4
  • 1
    What do you expect your output to be? – dfundako Jul 19 '18 at 18:52
  • Tip: It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. Note that `tsql` narrows the choices, but does not specify the database. – HABO Jul 20 '18 at 01:30

3 Answers3

2

To calculate the fiscal quarter, you can just subtract the month difference and calculate the "real" quarter:

DATEPART(quarter, DATEADD(month, 1-@firstMonthOfFiscalyear, Dates))

To calculate the start of the quarter, calculate the start of the "real" quarter for the date reduced by the month difference and finally add the month difference again. The start of the "real" quarter of a @date can be calculated as follows, making use of the fact that DATEDIFF returns an integer and so the division by 3 is an integer division (do not remove the brackets, the multiplication has to be done after the integer division):

DATEADD(month, 3*(DATEDIFF(month, 0, @date)/3), 0)

Replacing @date with Dates, reduced by @firstMonthOfFiscalyear-1 months and adding @firstMonthOfFiscalyear-1 months in the end, this will be

DATEADD(month, @firstMonthOfFiscalyear-1, DATEADD(month, 3*(DATEDIFF(month, 0, DATEADD(month, 1-@firstMonthOfFiscalyear, Dates))/3), 0))

This can be simplified a little to

DATEADD(month, @firstMonthOfFiscalyear-1 + 3*((DATEDIFF(month, 0, Dates)+1-@firstMonthOfFiscalyear)/3), 0)

So in the end, your query could look like this:

SELECT Dates
, DATEPART(quarter, DATEADD(month, 1-@firstMonthOfFiscalyear, Dates)) AS quarterNo
, DATEADD(month, @firstMonthOfFiscalyear-1 + 3*((DATEDIFF(month, 0, Dates)+1-@firstMonthOfFiscalyear)/3), 0) AS QuarterStartDate
FROM DateRange
Wolfgang Kais
  • 4,010
  • 2
  • 10
  • 17
0

Since the start of the year can be found using DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0), just add @firstMonthOfFiscalyear - 1 months:

DATEADD(mm,@firstMonthOfFiscalyear,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)

How to get the first and last date of the current year?

Or by building the string:

CAST(CAST(@firstMonthOfFiscalyear AS VARCHAR(2)) + '/1/' CAST(DATEPART(YY,GETDATE()) AS VARCHAR(4)) AS DATE)

Sam Cohen-Devries
  • 2,025
  • 2
  • 18
  • 35
0

You can try this query to get start date of next 4 quarters based on your input. use DATEFROMPARTS function to build start date of first quarter then use CTE to build start date of next quarters by adding 3 months to previous quarter.

    DECLARE @firstMonthOfFiscalyear int = 4
    ;WITH MyQuarters(q, qDate) as
    (
        select 1,    
         DATEFROMPARTS(year(getdate()), @firstMonthOfFiscalyear, 1) -- First quarter date
         UNION ALL
         select q+1,
         DATEADD(q, 1, qdate) -- next quarter start date
         from MyQuarters
         where q <4 -- limiting the number of next quarters
     )
    select * from MyQuarters

Output:

q   qDate
1   2018-04-01
2   2018-07-01
3   2018-10-01
4   2019-01-01
Vinit
  • 2,540
  • 1
  • 15
  • 22