SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Return current fiscal year data
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/06/2002 :  09:49:02  Show Profile  Visit AskSQLTeam's Homepage
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

USA
300 Posts

Posted - 09/06/2002 :  11:49:29  Show Profile
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

Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 09/06/2002 :  14:57:00  Show Profile
YEAR(DATEADD(month, 3 + DATEDIFF(month, 0, @dt), 0))


Go to Top of Page

littlewing
Starting Member

33 Posts

Posted - 07/05/2005 :  13:27:13  Show Profile
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/05/2005 :  14:56:20  Show Profile
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

Edited by - Michael Valentine Jones on 07/05/2005 16:01:19
Go to Top of Page

nosepicker
Constraint Violating Yak Guru

USA
366 Posts

Posted - 07/05/2005 :  15:02:38  Show Profile
Or, to borrow Arnold's previous post:

YEAR(DATEADD(month, DATEDIFF(month, 0, @dt) -5, 0))
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000