nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Definition Query with dates

GregorCalderwood1

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

Najděte více příspěvků označených jako

Desktop

Accepted answers

jcarlson

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)

All comments

Andrew_McLellan

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.

Dynamic SQL Date Query.jpg

AlinaTarmu_SCGIS

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)

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

Prab

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');

GregorCalderwood1

Hi Josh,

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

Thanks very much

jcarlson

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)

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.