DayOfYear as per Indian fiscal year

I want to get a sequence number according to the Indian fiscal year. I tried to write a function using SQL built-in function:

DATEPART(DAYOFYEAR,'2015-04-01')

but I am facing a challenge to get day number throughout the financial year.

For the financial year 2015: First Day is '2015-04-01' and Last Day is '2016-03-31'.

Note: For this period my financial year 2015 is a leap year, should have 366 days.

The sequence number of a day in a year that begins on april 1 can be calculated this way:

SELECT 1+
       DATEDIFF(day,
           DATEFROMPARTS(
               DATEPART(year, DATEADD(month, -3, [Date])),
               4,
               1),
           [Date]) AS FYdayNumber
FROM tableWithDates;

In essence, we're subtracting three months from the current date, then using that year to create a start-of-year date (year, april, 1), then calculating the number of days between that start-of-year date and the current date. Finally, add 1 in order to make the sequence start at 1.

If you just want the first day of your fiscal year, you can use

SELECT DATEFROMPARTS(
           DATEPART(year, DATEADD(month, -3, [Date])),
           4,
           1) AS firstDayOfFY
FROM tableWithDates;

As @MarkSinkinson pointed out in another answer, avoid (user-defined) scalar functions for performance and parallelisation reasons.

StackExchange Database Administrators Q#125842, answer score: 10