This relates to my question How to find XXth day of previous month in SQL server?

The fiscal month starts on 26th of each month.

I need to find out from any date:

  • The date of the start of the fiscal month (Start_Of_Fiscal_Month)
  • The number of days into the fiscal month (Day_Of_Fiscal_Month)

For example:

CurrentDate - '2014-04-26'

Start_Of_Fiscal_Month = 2014-04-26
Day_Of_Fiscal_Month = 1

CurrentDate - '2014-05-02'

Start_Of_Month = 2014-04-26
Day_Of_Fiscal_Month = 7

This will do it:

DECLARE @d DATE = '20140502', @dm TINYINT = 26;
DECLARE @Start_Of_Fiscal_Month DATE

SET @Start_Of_Fiscal_Month = DATEADD(DAY, @dm-1, DATEADD(MONTH, -1 + DATEPART(dd, @d)/26, DATEADD(DAY, 1-DAY(@d), @d)));
SELECT @Start_Of_Fiscal_Month
SELECT DATEDIFF(dd, @Start_Of_Fiscal_Month, @d) + 1
  • The formula can be further simplified to this: SET @Start_Of_Fiscal_Month = DATEADD(DAY, @dm-DAY(@d), DATEADD(MONTH, -1 + DATEPART(dd, @d)/26, @d)); Oct 29 '15 at 18:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.