Definition Query with dates

[LauraBoagey1]

Hi,  

I have 3 layers all pointing to one data source with different definition queries  by dates. However, someone pointed out that this can be done better as we have to change the year date every year and they said we can write a query that doesnt need changing every year, but they didnt have time to tell me what the query would be.... could anyone help?

Current year is anything post 1st April 2022

Previous year is anything between 1st April 2021 and 31st march 2022

Old data is 31st March 2021 and before

These are the queries we currently have

Current - DATE_LOGGED >= '2022-04-01 00:00:01'

Previous - DATE_LOGGED >= '2021-04-01 00:00:00' AND DATE_LOGGED <= '2022-03-31 00:00:00'

Old - DATE_LOGGED <= '2021-03-31 00:00:00'

How do I write the query so that I dont have to change the year in the query every 1st April?

Thanks

Laura

[Kara_Shindle]

SQL has a DATEFROMPARTS() function that could potentially work, but I don't know what your data source is.  I see you creating variables for the day and months for your different needs, and then calculating the years you need and building a date from that.

Here is another stack exchange article with a potential tip