1

My financial year is 01/04/20X0 to 31/03/20X1. So current financial year runs from 01/04/2018 to 31/03/2019. This is abbreviated to "20X0/X1" or "2018/19".

My output date is 01/08/2021 and is always of the format dd/mm/yyyy. This corresponds to Financial Year "2021/22"

I need help deriving a formula that is foolproof to return the financial year from my date or any given date of the format dd/mm/yyyy.

I do not want to use a Case statement as there will be several bands and does not seem efficient.

This is currently what I am using but is faulty

CAST(YEAR(DATEADD(month, 9, ISNULL(msdate5.[Completion - Revised], msdate5.[Completion - Original])))-1 as nvarchar) + '/' + CAST(YEAR(DATEADD(month, 9, ISNULL( msdate5.[Completion - Revised],  msdate5.[Completion - Original]))) as nvarchar)
Dasal Kalubowila
  • 103
  • 5
  • 22
  • What have you tried so far? – Thom A Aug 22 '18 at 11:52
  • 1
    When declaring a `(n)varchar`, `(n)char`, etc, always make sure you declare the length. I.e. `nvarchar(10)`. You can have some "nasty" surprises if you don't. – Thom A Aug 22 '18 at 11:57

3 Answers3

2

You can use this expression:

select (datename(year, dateadd(month, -3, @mydate)) + '/' +
        datename(year, dateadd(month, -3 + 12, @mydate))
       )

This offsets the date by three months and extracts the year. Note the use of datename() so you don't have to bother with converting anything to a string.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

For today, this returns '2018/19'. For things like this, one of the easiest methods is to align to fiscal year with the calendar year, and then work from there:

DECLARE @Date date = GETDATE();

SELECT CONVERT(varchar(4),DATEPART(YEAR, DATEADD(MONTH, -3, @Date))) + '/' + RIGHT(DATEPART(YEAR, DATEADD(MONTH, -3, @Date)) +1,2);
sticky bit
  • 36,626
  • 12
  • 31
  • 42
Thom A
  • 88,727
  • 11
  • 45
  • 75
0
DECLARE @mydate VARCHAR(20) = '01/08/2021'

SELECT cast(datepart(year, @mydate) AS VARCHAR(4)) + '/' + cast((datepart(year, @mydate) + 1) AS VARCHAR(4))

SELECT cast(cast(datepart(year, @mydate) AS VARCHAR(4)) + '/' + '04' + '/' + '01' AS DATETIME)
    ,cast(cast((datepart(year, @mydate) + 1) AS VARCHAR(4)) + '/' + '03' + '/' + '31' AS DATETIME)
KumarHarsh
  • 5,046
  • 1
  • 18
  • 22