Last day of current financial year

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Last day of current financial year

Author

Topic

jql
Starting Member

30 Posts

Posted - 2011-01-05 : 06:49:20

Hi,

i am trying to create some code that will work out the last day of the current financial year. e.g. type today’s date 05/01/2011 and the result would be 31/03/2011.
i gave got it to work from a calendar year

DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@end)+1,0)))
but struggling with the financial year. any ideas?

Regards

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-05 : 10:16:11

Is the end of the financial year always at the end of march? In that case something like this maybe...I know it's really simple but...:

DECLARE @end datetime = getdate()

SELECT CAST(CAST(YEAR(@end) AS char(4)) + '0331' AS datetime)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-05 : 10:21:10

select dateadd(month,3,DATEADD(year,datediff(year,0,getdate()),0))-1

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-05 : 10:56:10

[code]DECLARE @Sample TABLE
(
Today DATE
)

INSERT @Sample
VALUES ('20100215'),
('20100415')

SELECT Today,
DATEADD(YEAR, DATEDIFF(YEAR, '18991231', DATEADD(MONTH, -3, Today)), '19000301') AS CurrentFinancialYearLastDay
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

jql
Starting Member

30 Posts

Posted - 2011-01-06 : 02:07:14

Thank you all. now works

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-06 : 04:33:08

quote:

Originally posted by Peso

DECLARE	@Sample TABLE
	(
		Today DATE
	)

INSERT	@Sample
VALUES	('20100215'),
	('20100415')

SELECT	Today,
	DATEADD(YEAR, DATEDIFF(YEAR, '18991231', DATEADD(MONTH, -3, Today)), '19000301') AS CurrentFinancialYearLastDay
FROM	@Sample

N 56°04'39.26"
E 12°55'05.63"

FYI, the question is posted in 2005 forum

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources