locked
Last day of previous financial year RRS feed

  • Question

  • Hi I am trying to get the first and last day of previous financial year based on GETDATE(). Financial Year starts from 1 Oct - 30 Sep. 

    Eg. 10 Oct 2013 

    First Day of Previous Financial Year = 01 Oct 2012

    Last Day of Previous Financial Year = 30 Sep 2013

    I have tired several pieces of code but nothing seems to be working. Any help would be good.

    Thanks

    Tuesday, October 8, 2013 6:21 AM

Answers

  • I tried to plug in some dates instead of GETDATE() to make sure it will work properly for all the dates. When I replace GETDATE() with date of 20130930 last date = 20130930 when it should be 20120930

    Try

    select case when convert(datetime,'20130904',112)<convert(datetime,rtrim(convert(varchar,year(convert(datetime,'20130904',112))))+'1001',112) then convert(datetime,rtrim(convert(varchar,year(convert(datetime,'20130904',112))-2))+'1001',112) else convert(datetime,rtrim(convert(varchar,year(convert(datetime,'20130904',112))-1))+'1001',112) end [Period start],case when convert(datetime,'20130904',112)<convert(datetime,rtrim(convert(varchar,year(convert(datetime,'20130904',112))))+'0930',112) then convert(datetime,rtrim(convert(varchar,year(convert(datetime,'20130904',112))-1))+'0930',112) else convert(datetime,rtrim(convert(varchar,year(convert(datetime,'20130904',112))))+'0930',112) end [Period end]
    go
    



    Many Thanks & Best Regards, Hua Min


    • Edited by Jackson_1990 Tuesday, October 8, 2013 7:55 AM
    • Proposed as answer by Kalman Toth Tuesday, October 8, 2013 8:24 AM
    • Marked as answer by Allen Li - MSFT Wednesday, October 16, 2013 8:42 AM
    Tuesday, October 8, 2013 7:55 AM

All replies

  • Try this,

    SELECT 
    DATEADD(MM,-12,FY_START) PREVIOUS_FY_START,
    DATEADD(MM,-12,FY_END) PREVIOUS_FY_END
    FROM (
    	SELECT '10/01/2012' FY_START, '09/30/2013' FY_END
    ) X

    Regards, RSingh


    Tuesday, October 8, 2013 6:31 AM
  • Hi I am trying to get the first and last day of previous financial year based on GETDATE(). 

    What do you mean by "based on getdate()"?

    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Tuesday, October 8, 2013 6:31 AM
  • Try

    select case when getdate()<convert(datetime,rtrim(convert(varchar,year(getdate())))+'1001',112) then convert(datetime,rtrim(convert(varchar,year(getdate())-2))+'1001',112) else convert(datetime,rtrim(convert(varchar,year(getdate())-1))+'1001',112) end [Period start],case when getdate()<convert(datetime,rtrim(convert(varchar,year(getdate())))+'0930',112) then convert(datetime,rtrim(convert(varchar,year(getdate())-1))+'1001',112) else convert(datetime,rtrim(convert(varchar,year(getdate())))+'0930',112) end [Period end]
    go


    Many Thanks & Best Regards, Hua Min



    Tuesday, October 8, 2013 6:32 AM
  • I am trying to do something like this:

    select dateadd(month, datediff(month,0,GETDATE()) - (12  + datepart9month,GETDATE()) - 10) %24,0) -- First Day of previous financial year; but this is giving me the first day of current financial year.

    select dateadd(month, datediff(month,0,GETDATE()) - (12  + datepart9month,GETDATE()) - 10) % 12 + 12,-1) -- Last day of previous financial year; but this is giving me the last day of current financial year.

    This is probably a  really bad code. Can someone get me a better code that will do this for me.

    Thanks


    Tuesday, October 8, 2013 6:36 AM
  • Try my codes above.

    Many Thanks & Best Regards, Hua Min

    Tuesday, October 8, 2013 6:42 AM
  • That code doesn't work if the date is 20130930. 
    Tuesday, October 8, 2013 6:57 AM
  • Try this one:

    SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(YEAR, DATEADD(yy,-1,GETDATE()))) + '-10-01') AS [First Day of Previous Financial Year]
    	 , CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(YEAR, GETDATE())) + '-09-30') AS [Last Day of Previous Financial Year] 


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Tuesday, October 8, 2013 7:03 AM
  • That code still doesn't work if the date = 20130930. I need the first and last day of previous financial year. Financial is from 01 Oct to 30 Sep.

    Eg. 20130930

    First Day = 20111001

    Last Day = 20120930

    • Edited by pratheek956 Tuesday, October 8, 2013 7:15 AM
    Tuesday, October 8, 2013 7:08 AM
  • That code doesn't work if the date is 20130930. 
    My code is using getdate() to return the start/end range. What did you try?

    Many Thanks & Best Regards, Hua Min

    Tuesday, October 8, 2013 7:43 AM
  • I tried to plug in some dates instead of GETDATE() to make sure it will work properly for all the dates. When I replace GETDATE() with date of 20130930 last date = 20130930 when it should be 20120930
    Tuesday, October 8, 2013 7:45 AM
  • Try this:

    SELECT 
    	 CASE 
    		WHEN DATEPART(MONTH, getdate()) < 10
    			THEN CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(YEAR, DATEADD(yy,-2,getdate()))) + '-10-01') 
    		WHEN DATEPART(MONTH, getdate()) >= 10
    			THEN CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(YEAR, DATEADD(yy,-1,getdate()))) + '-10-01') 
    	 END AS [First Day of Previous Financial Year] ,
    	 CASE 
    		WHEN DATEPART(MONTH, getdate()) < 10
    			THEN CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(YEAR, DATEADD(yy,-1,getdate()))) + '-09-30') 
    		WHEN DATEPART(MONTH, getdate()) >= 10
    			THEN CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(YEAR, getdate())) + '-09-30') 
    	 END AS [First Day of Previous Financial Year] 


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    • Proposed as answer by Saeid Hasani Tuesday, October 8, 2013 8:55 AM
    Tuesday, October 8, 2013 7:48 AM
  • That code still doesn't work if the date = 20130930. I need the first and last day of previous financial year. Financial is from 01 Oct to 30 Sep.

    Eg. 20130930

    First Day = 20111001

    Last Day = 20120930

    Try the below:

    Declare @date date = '20130930'
    Select Cast(Year(DATEADD(month,-9,@date)) -1 as varchar(4)) + '1001' 'First day'
    ,Cast(Year(DATEADD(month,-9,@date)) as varchar(4)) + '0930' 'Last day'
    

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.



    • Edited by SQLZealots Tuesday, October 8, 2013 8:02 AM added first day
    Tuesday, October 8, 2013 7:52 AM
  • I tried to plug in some dates instead of GETDATE() to make sure it will work properly for all the dates. When I replace GETDATE() with date of 20130930 last date = 20130930 when it should be 20120930

    Try

    select case when convert(datetime,'20130904',112)<convert(datetime,rtrim(convert(varchar,year(convert(datetime,'20130904',112))))+'1001',112) then convert(datetime,rtrim(convert(varchar,year(convert(datetime,'20130904',112))-2))+'1001',112) else convert(datetime,rtrim(convert(varchar,year(convert(datetime,'20130904',112))-1))+'1001',112) end [Period start],case when convert(datetime,'20130904',112)<convert(datetime,rtrim(convert(varchar,year(convert(datetime,'20130904',112))))+'0930',112) then convert(datetime,rtrim(convert(varchar,year(convert(datetime,'20130904',112))-1))+'0930',112) else convert(datetime,rtrim(convert(varchar,year(convert(datetime,'20130904',112))))+'0930',112) end [Period end]
    go
    



    Many Thanks & Best Regards, Hua Min


    • Edited by Jackson_1990 Tuesday, October 8, 2013 7:55 AM
    • Proposed as answer by Kalman Toth Tuesday, October 8, 2013 8:24 AM
    • Marked as answer by Allen Li - MSFT Wednesday, October 16, 2013 8:42 AM
    Tuesday, October 8, 2013 7:55 AM
  • Declare @dt datetime = '20130930'
    --Set @dt='20131010'
     
    SELECT Dateadd(month, -3,DATEADD(year, DATEDIFF(year,0,dateadd(month,-9,@dt)),0) ) [First day of previous financial year]
    ,Dateadd(Day,-1,Dateadd(month, 9,DATEADD(year, DATEDIFF(year,0,dateadd(month,-9,@dt)),0) )) [Last day of previous financial year]

    Tuesday, October 8, 2013 3:38 PM
  • It is highly presumptive to propose your own posts as answers.  Please refrain from doing that.

    Tuesday, October 8, 2013 5:07 PM
  • I would recommend you do this with a Calendar table.  Calendar tables are tables with one row for each date and various other information about that date.  They are very useful for many types of date processing.  See http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html for how to create a calendar table and some of their many uses.

    But for example purposes, we can create a small temporary table with only the date and the fiscal year.  That would be

    -- Create temp calendar table
    Create Table #Calendar(dt date primary key, FY int);
    
    -- Load #Calendar with 65536 dates beginning with Jan 1, 1950
    -- That will be Jan 1, 1950 to June 6, 2129
    -- Real calendar tables might want more dates
    With 
    N2 As (Select 1 As Number Union All Select 1),
    N4 As (Select na.Number From N2 na Cross Join N2 nb),
    N16 As (Select na.Number From N4 na Cross Join N4 nb),
    N256 As (Select na.Number From N16 na Cross Join N16 nb),
    N65536 As (Select na.Number From N256 na Cross Join N256 nb),
    Numbers As (Select Row_Number() Over(Order By Number) As Number From N65536)
    Insert #Calendar(dt)
    Select DateAdd(day, Number - 1, '19500101')
    From Numbers;
    
    -- Now set the fiscal year value for each date in the table
    Update #Calendar
    Set FY = Case When Month(dt) <= 9 Then Year(dt) Else Year(dt) + 1 End;

    Now we can use that table to quickly and easily find the beginning and ending dates of the previous fiscal year with

    Select Min(dt) As FirstDayOfPreviousYear,
      Max(dt) As LastDayOfPreviousYear
    From #Calendar c
    Where c.FY = (select c2.FY - 1
      From #Calendar c2
      Where c2.dt = Cast(GetDate() as date));

    One advantage of using a calendar table for this sort of processing is you don't have to put code into your programs for things like what fiscal year is Aug 4, 2013.  You just need to have the correct values in your calendar table.  Then in a few years, if you accounting department comes to you and says "we're moving to a fiscal year from July 1 to June 30 starting in 2017 which means

    Fiscal year 2016 and earlier will be Oct 1 to Sept 30.
    Fiscal year 2017 will be a "short fiscal year" from Oct 1, 2017 to June 30, 2018.
    Fiscal year 2018 and following will be July 1 to June 30.
    
    

    To do this you will not have to find and change every place in your code where you calculate a fiscal year, you just update the calendar table with the new fiscal year values and your code will pick up the new fiscal year values.

    Tom



    Tuesday, October 8, 2013 10:19 PM