Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
up vote 5 down vote accepted

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
share|improve this answer
    
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)); – JoseTeixeira Oct 29 '15 at 18:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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