Find Fiscal Year date falls between.

Posted on 2007-10-04
Medium Priority
Last Modified: 2010-03-20
I have a MS SQL stored procedure that is supposed to find a rate in a table called class by the class code (@Class_code) and start date (@start_date).

To do this I need to determine the fiscal year the @start_date falls within and then find the Execution Date that is between the fiscal year.  Our fiscal year for example is 7/1/2007 to 6/31/2008.

I know how to do the select statement but cant figure to determine the fiscal year a date falls between. For example 7/31/2007 falls between the fiscal year 7/1/2007 to 6/31/2008.

Can anyone tell me what the code is to find the fiscal year?  My Boss wants this done ASAP.  

Class_code  Execution Date            Rate
TT                2007-08-01 00:00:01.000      .300
TT                2006-07-01 00:00:01.000      .240
TT                2005-07-01 00:00:01.000      .210
TT                2004-08-01 00:00:01.000      .220
TT                2003-08-01 00:00:01.000      .230
TT                2002-07-01 00:00:01.000      .230
TT                2001-07-01 00:00:01.000      .230
TT                1998-01-01 00:00:00.000      .170
Question by:fleet
  • 2
LVL 16

Expert Comment

ID: 20017311
select rate from table where CONVERT(VARCHAR, @start_date, 101) between '7/1/2007' to '6/31/2008'

Author Comment

ID: 20017440
Your solultion will only work if every @start_date falls within fiscal year 7/1/2007 to 6/31/2008. The start dates may fall into a different fiscal years.  Such as @start_date = 6/31/2005.  So I need to figure out which fiscal year each @start_date fall in.
LVL 19

Accepted Solution

folderol earned 2000 total points
ID: 20018382

create procedure findrate @startdate datetime, @classcode nvarchar(10)
declare @fystart datetime
declare @fyend datetime
declare @fy int

if cast('7/1/'+ datename(year,@startdate) as datetime) > @startdate
set @fy = 0
set @fy = 1
set @fystart = cast(cast(datepart(year,@startdate) -1 + @fy as varchar(4)) +'0701' as datetime)
Set @fyend   = cast(cast(datepart(year,@startdate) + @fy as varchar(4)) +'0630 23:59:59' as datetime)
select Rate from yourtable where class_code = @classcode and [Execution Date] between @fystart and @fyend


Author Comment

ID: 20022217
It worked great.  Thanks so much! You saved me  a lot of time.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question