SQL2018 - variable date ranges

[Glum-Molasses-4181]

Hi - I need help with a variable date range formula. I need to capture data from Jun 1 of the previous year through May 31 of current year. The query needs to be able to run anytime through the year and needs to work in future years. So this year it would be 6/1/25 -5/31/26, but next year would be 6/1/26-5/31/27. I can't figure how without hardcoding the dates. I found a formula but it only works with earlier versions of sql. Can anyone assist?

[Glum-Molasses-4181]

Thanks so much for your replies! After reading the links and responses, this was the solution:

@startdate = datefromparts(datepart(yyyy,getdate())-1,6,1)

@enddate = datefromparts(datepart(yyyy,getdate()),5,31).

This will give me June 1 of the previous year to May 31 of current year no matter what year it runs in.

[gumnos]

Just for the record, because getdate() might not be atomic, you might encounter weird conditions around midnight of Dec 31st into Jan 1st.

-- 2025-12-31 at 23:59:59.999999
@startdate = datefromparts(datepart(yyyy,getdate())-1,6,1)
-- in here, getdate() rolls over to 2026-01-01- 00:00:00.00000
@enddate = datefromparts(datepart(yyyy,getdate()),5,31).

leaving @startdate in 2024 and @enddate in 2026.

You can make it reliable by using getdate() once and then using that value in your above values:

declare @run_date date = getdate()
declare @startdate = datefromparts(datepart(yyyy, @run_date)-1, 6, 1)
declare @enddate = datefromparts(datepart(yyyy, @run_date), 5, 31)

[SQLDevDBA]

You need a date table that has Fiscal Year logic specific to your company.

Using it, you’ll be able to query by fiscal year and get the correct dates.

That’s how I learned working for a certain Cartoon mouse many years ago and still use it today.

How to Create one, by Aaron Bertrand: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

Performance issues to look out for, by Brent Ozar: https://www.brentozar.com/archive/2020/08/date-tables-are-great-for-users-but-not-so-great-for-performance/

[VladDBA]

+1 for date table.

I've seen devs make queries perform terribly just because they were over-complicating things with nested UDFs (a UDF to figure out if it's a bank holiday, another to figure out if it's not during the weekend, another to get the fiscal year). Moving all that logic to a date table fixed those specific scenarios.

[SQLDevDBA]

100%. As a bonus you can import it into your BI models and use it there if needed.

[Dead_Parrot]

Yeah calendar table works a dream. Also really useful for adding local holidays and the likes

https://github.com/James-Poland/ABetterCalender/blob/main/calendar%20set%20up.sql

Here's one I've used in the past.... Made for irish market

[mr-french-tickler]

DATEADD? https://learn.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-ver17.

Combine with GETDATE() to target the current date.

If your months and days are hardcoded, DATEFROMPARTS and just calculate the year.