topic Re: Definition Query with dates in ArcGIS Pro Questions

Definition Query with dates

GregorCalderwood1 — Thu, 01 Jul 2021 12:35:10 GMT

HI,

I really hope someone can help me out with this. I have a definition query defined on a feature class coming from an Oracle SDE where a date field is queried to return values within 6 months of the current date (see below)

RELDATE_DATE > ADD_MONTHS(TO_DATE(SYSDATE),-6

We are in the process of migrating to a PostgreSQL SDE and with the same feature class the query no longer works or is verified ( returns an error)

What should my new definition query look like?

The feature class filed being queried is RELDATE_DATE

Thanks

Gregor

Re: Definition Query with dates

jcarlson — Thu, 01 Jul 2021 13:20:22 GMT

Try this:

RELDATE_DATE > (NOW() - interval '6 months')

EDIT:

It looks like you can't use those functions in Pro, even if the DB itself would in a query. Try this instead:

RELDATE_DATE > (CURRENT_DATE - 182)

Re: Definition Query with dates

GregorCalderwood1 — Thu, 01 Jul 2021 13:43:14 GMT

Hi Josh,

Thanks for the response - the RELDATE_DATE > (CURRENT_DATE -182) works.

Thanks very much

maybe this will help? Re: Definition Query with dates

Prab — Fri, 02 Jul 2021 15:37:36 GMT

Classification: UNCLASSIFIED
======================================================

Gregor,

Try the following found in

https://stackoverflow.com/questions/1888544/how-to-select-records-from-last-24-hours-using-sql

In MySQL:

SELECT *

FROM mytable

WHERE record_date >= NOW() - INTERVAL 1 DAY

In SQL Server:

SELECT *

FROM mytable

WHERE record_date >= DATEADD(day, -1, GETDATE())

In Oracle:

SELECT *

FROM mytable

WHERE record_date >= SYSDATE - 1

In PostgreSQL:

SELECT *

FROM mytable

WHERE record_date >= NOW() - '1 day'::INTERVAL

In Redshift:

SELECT *

FROM mytable

WHERE record_date >= GETDATE() - '1 day'::INTERVAL

In SQLite:

SELECT *

FROM mytable

WHERE record_date >= datetime('now','-1 day')

In MS Access:

SELECT *

FROM mytable

WHERE record_date >= (Now - 1)

select * from table where date_field > (now() - interval '24 hour');

Re: Definition Query with dates

LauraBoagey1 — Tue, 28 Feb 2023 14:09:00 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

AlinaTarmu_SCGIS — Mon, 22 Apr 2024 16:09:00 GMT

I have a similar issue with a definition query. This

RELDATE_DATE > (CURRENT_DATE - 182)

works when it is not combined with other conditions. The moment I add others, it throws an invalid syntax error. Here is my expression:

(CLASS = 'first' Or CLASS = 'second' Or CLASS = 'third' Or CLASS = 'fourth' Or CLASS = 'fifth' Or CLASS = 'sixth' Or CLASS = 'seventh' Or CLASS = 'eighth') And EQUIP = 'X' And Date_Created > (CURRENT_DATE - 182)

Basically the bolded part works, but when I add the date filter, I get an error. (I am in Pro 3.2.1, to clarify).

@jcarlson wrote:
Try this:
RELDATE_DATE > (NOW() - interval '6 months')
EDIT:
It looks like you can't use those functions in Pro, even if the DB itself would in a query. Try this instead:
RELDATE_DATE > (CURRENT_DATE - 182)

Re: Definition Query with dates

Andrew_McLellan — Wed, 07 Jan 2026 18:51:39 GMT

Hi,

I know this is an old post, but I believe there is a way to do this now in Arc Pro Definition Query with SQL

EXTRACT(YEAR FROM "Date_Field") = EXTRACT(YEAR FROM CURRENT_DATE - 365)

This will allow someone to have way to auto filter years in a date field by the current date year +- however many days. This might be helpful to others rather than hard coding dates in the definition queries if the map is used year over year on a living data set.