Last day of previous financial year

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

Try my codes above.

---

Many Thanks & Best Regards, Hua Min

Answer 6

That code doesn't work if the date is 20130930. 

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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.

Answer 13

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

Answer 14

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]

Answer 15

It is highly presumptive to propose your own posts as answers.  Please refrain from doing that.

Answer 16

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