Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

1 Answer 1

up vote 3 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

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.