Return First Day Of Financial Year(april 1st) T-sql

First Day of financial year is April 1st. T-SQL Query to return April 1st for the getdate() Financial Year: April 1st to March 31st

Solution 1:

Try this:

select DATEFROMPARTS(Yr, 4, 1) [start], DATEFROMPARTS(Yr +1, 3, 31) [end] from 
(selectcasewhen DATEPART(month, getdate()) <4then DATEPART(year, getdate()) -1else DATEPART(year, getdate()) end Yr) a

Solution 2:

declare@todaydate='2018-06-21'select  fin_year = dateadd(month, 3, 
                           dateadd(year, 
                                   datepart(year, 
                                            dateadd(month, -3, @today)) -1900, 0))

the expression datepart(year, dateadd(month, -3, @today)) is to get the current financial year. Since your financial year is Apr 1, for Jan 1 to Mar 31, subtracting 3 months from it, will give you the correct year (fiscal year = financial year).

After that it is just to form the date Apr 1 with that year

Solution 3:

DECLARE@DateToUse DATETIME = GETDATE(),
        @FinancialYearStart DATETIME
DECLARE@DayPartINT= DATEPART(DAY, @DateToUse),
        @MonthPartINT= DATEPART(MONTH, @DateToUse),
        @YearPartINT= DATEPART(YEAR, @DateToUse),
        @StartMonthINT=4, -- April@StartDayINT=1-- 1stSELECT DATETIMEFROMPARTS((
        @YearPart-CASEWHEN@MonthPart>@StartMonthOR (
                    @MonthPart=@StartMonthAND@DayPart>=@StartDay
                    )
                THEN0ELSE1END
        ), @StartMonth, @StartDay, 0, 0, 0, 0)

I just knocked this up and it produces 2020-04-01 00:00:00.000right now. I threw some other dates at it and it seemed to work nicely. Obviously the month/day can easily be changed by changing the variables at the top.

It's not the shortest block of SQL, but it's easy to use for different dates. A lot of other answers I've seen across different questions have returned different formats like a string or just the year part. Whereas this datetime will allow it to be used for datetime comparisons.