Link to home
Create AccountLog in
Avatar of fleet
fleet

asked on

Find Fiscal Year date falls between.

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
Avatar of SQL_SERVER_DBA
SQL_SERVER_DBA
Flag of United States of America image

select rate from table where CONVERT(VARCHAR, @start_date, 101) between '7/1/2007' to '6/31/2008'
Avatar of fleet
fleet

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of folderol
folderol

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of fleet

ASKER

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