DATEPART to calculate financial year RRS feed

  • Question

  • Hello

    I have a report that uses the Datepart function to return the current, and previous, year's data:

    DATEPART(YEAR, a.Date);
    DATEPART(YEAR, a.Date) = DATPART(YEAR, GetDate())-1

    This brings through calendar year fine.

    Is it possible to amend this to bring through the financial year (e.g. 1/4/15 - 31/3/16)?

    Any advice much appreciated.
    Tuesday, September 15, 2015 7:09 AM


  • Hi William 

    If i read your question correctly you are trying to get the fiscal year that a date lies in. and that you fiscal year starts 4 months after the calendar year (April). to do this you can use the DATEADD function

    DECLARE @MyDate DATE = '2016-04-01'
    SELECT YEAR(DATEADD(MM,(12-(4-1)),@MyDate)) [FiscalYearWith Calc],YEAR(DATEADD(MM,(9),@MyDate)) [FiscalYear]

    Tuesday, September 15, 2015 8:07 AM