September 24, 2009 at 7:40 am
We have a fiscal year (10/01/Year > 09/30/Year). I would like to build a process to determine the start and end dates of the "current" fiscal year based on the current date. For example:
If current date is 09/15/2009, I would like to determine for subsequent processing of the query the date parameters would select data from 10/01/2008 > 09/30/2009.
If current date is 11/01/2009, I would like to determine for subsequent processing of the query the date parameters would select data from 10/01/2009 > 09/30/2010 (or current date).
Thanks in advance.
September 24, 2009 at 8:07 am
you can use a proc with output parameters. Then you could use some logic on the month to determine which fiscal year the date is in
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 24, 2009 at 8:37 am
This will calculate the FY start and and date for any given date.
select
a.DT,
FYStartDate = dateadd(mm,9,dateadd(yy,datediff(yy,0,dateadd(mm,-9,a.DT)),0)),
FYEndDate = dateadd(mm,9,dateadd(yy,datediff(yy,-1,dateadd(mm,-9,a.DT)),-1))
from
( -- Test Data
select DT = getdate() union all
select DT = '20081001' union all
select DT = '20090930 23:59:59.997' union all
select DT = '20091001' union all
select DT = '20100930 23:59:59.997'
) a
order by
a.DT
Results:
DT FYStartDate FYEndDate
----------------------- ----------------------- -----------------------
2008-10-01 00:00:00.000 2008-10-01 00:00:00.000 2009-09-30 00:00:00.000
2009-09-24 12:20:37.533 2008-10-01 00:00:00.000 2009-09-30 00:00:00.000
2009-09-30 23:59:59.997 2008-10-01 00:00:00.000 2009-09-30 00:00:00.000
2009-10-01 00:00:00.000 2009-10-01 00:00:00.000 2010-09-30 00:00:00.000
2010-09-30 23:59:59.997 2009-10-01 00:00:00.000 2010-09-30 00:00:00.000
Note: Reposted after fixing logic.
September 24, 2009 at 8:43 am
If you're doing a lot of work with dates and financial years for reporting, I would strongly recommend creating a well indexed date table that you can use as a lookup. It will save a lot of headaches - especially if your users create reports themselves. You could use the below SQL to populate a table:
with mycte as
(
select cast('2000-01-01' as datetime) DateValue
union all
select DateValue + 1
from mycte
where DateValue + 1 = 10) then
convert(datetime,convert(char(4),datepart(yy, DateValue)) + '-10-01' )
else
convert(datetime,convert(char(4),datepart(yy, DateValue)-1) + '-10-01' )
end as Financial_Year_Start
, case when (datepart(mm, DateValue) >= 10) then
convert(datetime,convert(char(4),datepart(yy, DateValue)+1) + '-09-30' )
else
convert(datetime,convert(char(4),datepart(yy, DateValue)) + '-09-30' )
end as Financial_Year_Start
from mycte
OPTION (MAXRECURSION 0)
You could also add things like month start and end dates, calendar year start/calendar year end etc.
P.S. Claiming no credit for this code, it's adapted from this blog:
http://consultingblogs.emc.com/jamespipe/archive/2007/04/24/T_2D00_SQL-Calendar-table.aspx
September 24, 2009 at 8:54 am
declare @vdt_date smalldatetime
set @vdt_date = getdate()
set @vdt_date = '2009-09-30'
set @vdt_date = '2009-10-01'
select @vdt_date,
case when month(@vdt_date) >= 10 then
cast('10/1/' + cast(year(@vdt_date) as varchar) as smalldatetime)
else
cast('10/1/' + cast((year(@vdt_date) - 1) as varchar) as smalldatetime)
end StartDate,
case when month(@vdt_date) >= 10 then
cast('9/30/' + cast((year(@vdt_date) + 1) as varchar) as smalldatetime)
else
cast('9/30/' + cast(year(@vdt_date) as varchar) as smalldatetime)
end EndDate
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 24, 2009 at 10:46 am
How about trying this:
declare @Date datetime;
set @Date = '2009-09-15';
select
dateadd(mm, -3, dateadd(yy, datediff(yy, 0, dateadd(mm, 3, @Date)), 0)) as BeginFiscalYear,
dateadd(dd, -1, dateadd(yy, 1, dateadd(mm, -3, dateadd(yy, datediff(yy, 0, dateadd(mm, 3, @Date)), 0)))) as EndFiscalYear
set @Date = '2009-11-01';
select
dateadd(mm, -3, dateadd(yy, datediff(yy, 0, dateadd(mm, 3, @Date)), 0)) as BeginFiscalYear,
dateadd(dd, -1, dateadd(yy, 1, dateadd(mm, -3, dateadd(yy, datediff(yy, 0, dateadd(mm, 3, @Date)), 0)))) as EndFiscalYear
September 24, 2009 at 10:48 am
Actually, Michael Valentine Jones looks simplier than mine, so I'd go with his. I wouldn't have posted mine had I read his first, but I got sidetracked a bit here at work and didn't check first before posting.
September 24, 2009 at 10:53 am
Taking Michael Valentine Jones version and making it a bit more the way I'd write it, it would look like this:
select
a.DT,
FYStartDate = dateadd(mm, 9,dateadd(yy,datediff(yy,0,dateadd(mm,-9,a.DT)),0)),
FYEndDate = dateadd(mm, 9,dateadd(yy,datediff(yy,0,dateadd(mm,-9,a.DT))+1,0))
from
( -- Test Data
select DT = getdate() union all
select DT = '20081001' union all
select DT = '20090930 23:59:59.997' union all
select DT = '20091001' union all
select DT = '20100930 23:59:59.997'
) a
order by
a.DT
September 24, 2009 at 1:43 pm
Lynn Pettis (9/24/2009)
Taking Michael Valentine Jones version and making it a bit more the way I'd write it, it would look like this:
select
a.DT,
FYStartDate = dateadd(mm, 9,dateadd(yy,datediff(yy,0,dateadd(mm,-9,a.DT)),0)),
FYEndDate = dateadd(mm, 9,dateadd(yy,datediff(yy,0,dateadd(mm,-9,a.DT))+1,0))
from
( -- Test Data
select DT = getdate() union all
select DT = '20081001' union all
select DT = '20090930 23:59:59.997' union all
select DT = '20091001' union all
select DT = '20100930 23:59:59.997'
) a
order by
a.DT
That produces different results than my code. My FYEndDate is the last day of the fiscal year (2009-09-30) while your FYEndDate is the first day of the next fiscal year (2009-10-01).
September 24, 2009 at 4:07 pm
Michael Valentine Jones (9/24/2009)
Lynn Pettis (9/24/2009)
Taking Michael Valentine Jones version and making it a bit more the way I'd write it, it would look like this:
select
a.DT,
FYStartDate = dateadd(mm, 9,dateadd(yy,datediff(yy,0,dateadd(mm,-9,a.DT)),0)),
FYEndDate = dateadd(mm, 9,dateadd(yy,datediff(yy,0,dateadd(mm,-9,a.DT))+1,0))
from
( -- Test Data
select DT = getdate() union all
select DT = '20081001' union all
select DT = '20090930 23:59:59.997' union all
select DT = '20091001' union all
select DT = '20100930 23:59:59.997'
) a
order by
a.DT
That produces different results than my code. My FYEndDate is the last day of the fiscal year (2009-09-30) while your FYEndDate is the first day of the next fiscal year (2009-10-01).
Good catch. That's what happens when you run a global replace and don't catch it.
Here is the code it should be:
select
a.DT,
FYStartDate = dateadd(mm, 9,dateadd(yy,datediff(yy,0,dateadd(mm,-9,a.DT)),0)),
FYEndDate = dateadd(mm, 9,dateadd(yy,datediff(yy,0,dateadd(mm,-9,a.DT))+1, -1))
from
( -- Test Data
select DT = getdate() union all
select DT = '20081001' union all
select DT = '20090930 23:59:59.997' union all
select DT = '20091001' union all
select DT = '20100930 23:59:59.997'
) a
order by
a.DT
September 28, 2009 at 1:29 pm
Thanks (to all) for your help. This worked perfectly.
September 28, 2009 at 2:28 pm
Michael Valentine Jones (9/24/2009)
Lynn Pettis (9/24/2009)
Taking Michael Valentine Jones version and making it a bit more the way I'd write it, it would look like this:
select
a.DT,
FYStartDate = dateadd(mm, 9,dateadd(yy,datediff(yy,0,dateadd(mm,-9,a.DT)),0)),
FYEndDate = dateadd(mm, 9,dateadd(yy,datediff(yy,0,dateadd(mm,-9,a.DT))+1,0))
from
( -- Test Data
select DT = getdate() union all
select DT = '20081001' union all
select DT = '20090930 23:59:59.997' union all
select DT = '20091001' union all
select DT = '20100930 23:59:59.997'
) a
order by
a.DT
That produces different results than my code. My FYEndDate is the last day of the fiscal year (2009-09-30) while your FYEndDate is the first day of the next fiscal year (2009-10-01).
Which might actually be preferable depending upon how you are going to use the dates. If you use FYEndDate = last day of the fiscal year, then you have to make sure your queries use the following (if your date columns include time):
WHERE somedatecolumn >= @FYStartDate
AND somedatecolumn < dateadd(day, 1, @FYEndDate)
If you have @FYEndDate = first day of next fiscal year - then your queries would be:
WHERE somedatecolumn >= @FYStartDate
AND somedatecolumn < @FYEndDate
Either way works - but I would (personally) prefer the first method using the end date as the true end date of the fiscal year and making sure the queries use it correctly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 29, 2009 at 8:48 am
Jeffrey Williams-493691 (9/28/2009)
Michael Valentine Jones (9/24/2009)
Lynn Pettis (9/24/2009)
Taking Michael Valentine Jones version and making it a bit more the way I'd write it, it would look like this:
select
a.DT,
FYStartDate = dateadd(mm, 9,dateadd(yy,datediff(yy,0,dateadd(mm,-9,a.DT)),0)),
FYEndDate = dateadd(mm, 9,dateadd(yy,datediff(yy,0,dateadd(mm,-9,a.DT))+1,0))
from
( -- Test Data
select DT = getdate() union all
select DT = '20081001' union all
select DT = '20090930 23:59:59.997' union all
select DT = '20091001' union all
select DT = '20100930 23:59:59.997'
) a
order by
a.DT
That produces different results than my code. My FYEndDate is the last day of the fiscal year (2009-09-30) while your FYEndDate is the first day of the next fiscal year (2009-10-01).
Which might actually be preferable depending upon how you are going to use the dates. If you use FYEndDate = last day of the fiscal year, then you have to make sure your queries use the following (if your date columns include time):
WHERE somedatecolumn >= @FYStartDate
AND somedatecolumn < dateadd(day, 1, @FYEndDate)
If you have @FYEndDate = first day of next fiscal year - then your queries would be:
WHERE somedatecolumn >= @FYStartDate
AND somedatecolumn < @FYEndDate
Either way works - but I would (personally) prefer the first method using the end date as the true end date of the fiscal year and making sure the queries use it correctly.
In a sense you only need fiscal year begin date @FYStartDate then query could be
WHERE somedatecolumn >= @FYStartDate
AND somedatecolumn < dateadd(Year, 1, @FYStartDate) -- start of next fiscal year
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply