Definition Query with dates

LauraBoagey1 — Tue, 28 Feb 2023 14:12:45 GMT

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

Re: Definition Query with dates

Kara_Shindle — Tue, 28 Feb 2023 15:50:45 GMT

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

topic Definition Query with dates in ArcMap Questions

Definition Query with dates

Re: Definition Query with dates