Return current fiscal year data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Return current fiscal year data

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-06 : 09:49:02

Harold writes "I have multiple years worth of data and would like to return the current fiscal years data only. In my example records from Oct. 01 through Sept 31 02 would be this fiscal year."

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-09-06 : 11:49:29

Harold,

What version of SQL Server are you using? 7.0 or 2000? If you are using 2000, then I would set up a User Defined Function to turn a date value into a fiscal year or fiscal period. This is the function I created to convert to a fiscal period. Review it and modify as needed.

CREATE FUNCTION dbo.TO_FISCALPERIOD
(@Date DATETIME)
RETURNS CHAR(4) AS
BEGIN
DECLARE @new_date char(6)
SELECT @new_date = Case Month(@Date)
WHEN 1 THEN CAST(YEAR(@Date) AS CHAR(4)) +'04'
WHEN 2 THEN CAST(YEAR(@Date) AS CHAR(4)) +'05'
WHEN 3 THEN CAST(YEAR(@Date) AS CHAR(4)) +'06'
WHEN 4 THEN CAST(YEAR(@Date) AS CHAR(4)) +'07'
WHEN 5 THEN CAST(YEAR(@Date) AS CHAR(4)) +'08'
WHEN 6 THEN CAST(YEAR(@Date) AS CHAR(4)) +'09'
WHEN 7 THEN CAST(YEAR(@Date) AS CHAR(4)) +'10'
WHEN 8 THEN CAST(YEAR(@Date) AS CHAR(4)) +'11'
WHEN 9 THEN CAST(YEAR(@Date) AS CHAR(4)) +'12'
WHEN 10 THEN CAST(YEAR(dateadd(yy,1,@Date)) AS CHAR(4)) + '01'
WHEN 11 THEN CAST(YEAR(dateadd(yy,1,@Date)) AS CHAR(4)) + '02'
WHEN 12 THEN CAST(YEAR(dateadd(yy,1,@Date)) AS CHAR(4)) + '03'
END
RETURN @new_date
END

This function could probably be optimized better, but it gives you the general idea.

Jeremy

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-09-06 : 14:57:00

YEAR(DATEADD(month, 3 + DATEDIFF(month, 0, @dt), 0))

littlewing
Starting Member

33 Posts

Posted - 2005-07-05 : 13:27:13

I have a somewhat similar situation except my fiscal year spans June to May of following year. Can anyone help me with a user defined function to return the fiscal year of the current date?

For example if the current date is between June 1 2005 and May 31 2006 then my fiscal year would be 2005. Similarly if the current date is between June 1 2006 and May 31 2007 then fiscal year would be 2006.

Returning the fiscal year as a char(4) would be great.

Thanks very much.

LW

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-05 : 14:56:20

Any reason qhy a query like this would not work?

select
	col1,col2, ...etc.
from
	MyTable
where
	myDateCol >= '2004/10/1 00:00:00'	and
	myDateCol <  '2005/11/1 00:00:00'

quote:
Originally posted by AskSQLTeam

Harold writes "I have multiple years worth of data and would like to return the current fiscal years data only. In my example records from Oct. 01 through Sept 31 02 would be this fiscal year."

CODO ERGO SUM

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-05 : 15:02:38

Or, to borrow Arnold's previous post:

YEAR(DATEADD(month, DATEDIFF(month, 0, @dt) -5, 0))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources