Script to calculate Fiscal Year from Date

  • SQLisAwe5oMe

    SSChampion

    Points: 13851

    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.

  • Bill-89778

    Ten Centuries

    Points: 1071

    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: 51272

    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: 183567

    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: 109670

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

    FROM dbo.calendar c

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

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

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