Calculate fiscal month for the Calendar Year.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

My organization follows the calendar year from Jan 1st to Dec 31st every year.
But for reporting purposes we go by the fiscal month of the calendar year.

Our fiscal month starts on the first Wednesday of every calendar month and ends on the last
Wednesday of the calendar month except for every Quarter end
i.e. for March 31, June 30, Sept 30 and Dec 31 of the year the fiscal month ends on the last day of that respective
quarter.
I need a function or a query that calculates the fiscal month for any given date of the form mm/dd/yyyy e.g. 10/25/2012
Any help would be much appreciated.

Posted 15-Jul-13 19:18pm

aakar

Add a Solution

Comments

Jignesh Khant 16-Jul-13 1:57am

Do you want to calculate no of wednesday's within a particular range of dates?

Raja Sekhar S 16-Jul-13 7:43am

Can you Provide an Example... use Improve Question Widget...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-07-19T11:54:00

Test it (version for MS SQL Server 20005):

SQL

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET DATEFORMAT ymd;

SET @startDate = CONVERT(DATETIME, CONVERT(VARCHAR(10),YEAR(GETDATE())) + '-' + CONVERT(VARCHAR(10),MONTH(GETDATE())) + '-01')
SET @endDate = DATEADD(dd,-1,DATEADD(mm,1,@startDate))

--comment below lines to change to current month
SET @startDate = '2013-06-01'
SET @endDate = '2013-06-30'
--SELECT @startDate AS sDate, @endDate AS eDate

;WITH fullmonth AS
(
	SELECT @startDate AS aDate, DATENAME(dw,@startDate) AS NameOfDate
	UNION ALL
	SELECT DATEADD(dd,1,aDate) AS aDate, DATENAME(dw,DATEADD(dd,1,aDate)) AS NameOfDate
	FROM fullmonth
	WHERE aDate<@endDate
)
SELECT CONVERT(VARCHAR(10),aDate,121) AS aDate, NameOfDate
FROM (
	SELECT *,
		(SELECT MIN(aDate) FROM fullmonth WHERE NameOfDate = 'Wednesday') AS MinDate,
		(SELECT MAX(aDate) FROM fullmonth  WHERE NameOfDate = 'Wednesday' AND MONTH(aDate) NOT IN (3,6,9,12)) AS MaxDate
	FROM fullmonth
	) AS T
WHERE aDate BETWEEN MinDate AND COALESCE(MaxDate, @endDate)

Above code generates set of dates for actual month ;)

2013-06-05	Wednesday
2013-06-06	Thursday
2013-06-07	Friday
2013-06-08	Saturday
2013-06-09	Sunday
2013-06-10	Monday
2013-06-11	Tuesday
2013-06-12	Wednesday
2013-06-13	Thursday
2013-06-14	Friday
2013-06-15	Saturday
2013-06-16	Sunday
2013-06-17	Monday
2013-06-18	Tuesday
2013-06-19	Wednesday
2013-06-20	Thursday
2013-06-21	Friday
2013-06-22	Saturday
2013-06-23	Sunday
2013-06-24	Monday
2013-06-25	Tuesday
2013-06-26	Wednesday
2013-06-27	Thursday
2013-06-28	Friday
2013-06-29	Saturday
2013-06-30	Sunday