How to find number of days since beginning of fiscal month?

Question

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

Answer 1

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