Help on creating Fiscal Year 12 Periods

Question

Hi,

I am developing a financial application using SQLServer 2008 as database and I want to generate fiscal periods of any year for example

Input : June 2010

Results desired 12 periods from the starting date of June 2010 - May -2011

Period…………….Start…………………………....………..End 1…...………………1-06-2010……………………………30-06-2010 2..........1-07-2010...........31-07-2010

And so on untill May 2010 …Actually need to get 12 rows (12 months) starting from any month and output will be the starting date of the month and end would be the last day of the month like for example in my scenario June 2010 - May 2011 ..what will be the query.

Thanks in Advance

Answer 1

See DATEADD()

select convert(datetime,'6/1/2010')

select DATEADD(day,-1,DATEADD(month,1,convert(datetime,'6/1/2010')))   --6/30/2010

select DATEADD(month,1,convert(datetime,'6/1/2010'))   --7/1/2010

You would likely run this in a stored procedure which would generate a temp table, or quick and really dirty just one big query...

I hesitate to just do the whole thing for you, but what the hay...

create procedure getFiscalYear
  @startDate nvarchar(10)
as begin

declare @myDate datetime
set @myDate = convert(datetime,@startDate)

select 1 as Period, @myDate as startDate, DATEADD(day,-1,DATEADD(month,1,@myDate)) as endDate
union
select 2 as Period, DATEADD(month,1,@myDate) as startDate, DATEADD(day,-1,DATEADD(month,2,@myDate)) as endDate
union
select 3 as Period, DATEADD(month,2,@myDate) as startDate, DATEADD(day,-1,DATEADD(month,3,@myDate)) as endDate
union
select 4 as Period, DATEADD(month,3,@myDate) as startDate, DATEADD(day,-1,DATEADD(month,4,@myDate)) as endDate
union
select 5 as Period, DATEADD(month,4,@myDate) as startDate, DATEADD(day,-1,DATEADD(month,5,@myDate)) as endDate
union
select 6 as Period, DATEADD(month,5,@myDate) as startDate, DATEADD(day,-1,DATEADD(month,6,@myDate)) as endDate
union
select 7 as Period, DATEADD(month,6,@myDate) as startDate, DATEADD(day,-1,DATEADD(month,7,@myDate)) as endDate
union
select 8 as Period, DATEADD(month,7,@myDate) as startDate, DATEADD(day,-1,DATEADD(month,8,@myDate)) as endDate
union
select 9 as Period, DATEADD(month,8,@myDate) as startDate, DATEADD(day,-1,DATEADD(month,9,@myDate)) as endDate
union
select 10 as Period, DATEADD(month,9,@myDate) as startDate, DATEADD(day,-1,DATEADD(month,10,@myDate)) as endDate
union
select 11 as Period, DATEADD(month,10,@myDate) as startDate, DATEADD(day,-1,DATEADD(month,11,@myDate)) as endDate
union
select 12 as Period, DATEADD(month,11,@myDate) as startDate, DATEADD(day,-1,DATEADD(month,12,@myDate)) as endDate

end

that's the ugly way...

Answer 2

Here's a method using a common table expression to create a list of periods. I'm borrowing @Fosco's DATEADD approach (thx @Fosco).

DECLARE @StartDate DATETIME = '06/1/2010';

WITH periods AS
    (SELECT 1 AS period
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 4
    UNION SELECT 5
    UNION SELECT 6
    UNION SELECT 7
    UNION SELECT 8
    UNION SELECT 9
    UNION SELECT 10
    UNION SELECT 11
    UNION SELECT 12
    )

SELECT period,
    DATEADD(month,period-1,@StartDate) as startDate,
    DATEADD(day,-1,DATEADD(month,period,@StartDate)) as endDate
FROM periods

There are a number of ways to create your list of periods. See this for examples.