Fiscal Year - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Fiscal Year

Author

Topic

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-06-08 : 10:10:13

Do any one know a fiscal year code with the fiscal year begins 1-Oct to last day of Sept.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-08 : 10:44:03

What is the input and expected output ?

KH
[spoiler]Time is always against us[/spoiler]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-08 : 11:24:09

I'd suggest you create some sort of Date table for this information. But, here is one way to generate the fiscal year start and end based on the current date:

SELECT
	DATEADD(YEAR, (YEAR(SYSDATETIME()) - 1900) - CASE WHEN MONTH(SYSDATETIME()) IN (10, 11, 12) THEN 0 ELSE 1 END, '19001001') FiscalYearStart,
	DATEADD(YEAR, (YEAR(SYSDATETIME()) - 1900) + CASE WHEN MONTH(SYSDATETIME()) IN (10, 11, 12) THEN 1 ELSE 0 END, '19000930') FiscalYearEnd

EDIT: Had an addition/subtraction sign backwards

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-08 : 11:52:48

This is from 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

DECLARE @FiscalStart datetime
DECLARE @ThisDate datetime

SET @FiscalStart = '19001001'
SET @Thisdate = current_timestamp

SELECT DATEADD(YEAR, DATEDIFF(MONTH, @FiscalStart, @Thisdate) / 12, @FiscalStart) AS FiscalYearStart

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources