15,614,766 members
0.00/5 (No votes)
See more:
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
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...

## Solution 1

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```

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

Top Experts
Last 24hrsThis month
 OriginalGriff 110 raddevus 50 Richard MacCutchan 40 CHill60 35 Graeme_Grant 25
 OriginalGriff 2,966 Richard MacCutchan 1,266 Graeme_Grant 965 Dave Kreskowiak 649 CHill60 360

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900