Find Fiscal Year date falls between.

Posted on 2007-10-04
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
    LVL 16

    Expert Comment

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

    Author Comment

    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


    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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now