Script to calculate Fiscal Year from Date

Question

Script to calculate Fiscal Year from Date

SQLisAwe5oMe

SSChampion

Points: 14047
More actions
May 3, 2016 at 1:09 pm

#310335

Hi,
Anyone has a script to calculate the fiscal year from a date.
Basically our FY starts from 9/1 and end at 8/31 following yr.
Example FY17 will be 9/1/16 through 8/31/17.
Regards,
SQLisAwe5oMe.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Bill-89778 Ten Centuries Points: 1201 More actions · Answer 1

I would suggest building a 'Calendar' table that contains fields that contains fields pertaining to your fiscal year and fiscal period information. It could also include the regular calendar information for reference.

HTH

Bill

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

DECLARE @calendardate datetime

SET @calendardate = '20160901'

SELECT fiscal_year = CASE

WHEN DATEPART(M, @calendardate) >= 9

THEN DATEPART(YEAR, @calendardate) + 1

ELSE DATEPART(YEAR, @calendardate)

END;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Like this?

SELECT CASE WHEN RIGHT(CONVERT(char(8), GETDATE(), 112), 4) < '0901'

THEN YEAR(GETDATE()) - 1

ELSE YEAR(GETDATE()) END

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sergiy SSC Guru Points: 110206 More actions · Answer 4

SELECT date, YEAR(DATEADD(mm, 4, c.Date))%100 FY

FROM dbo.calendar c

WHERE YEAR(DATEADD(mm, 4, c.Date))%100 = 17

_____________
Code for TallyGenerator