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

[jcarlson · Accepted solution]

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)

[GregorCalderwood1]

Hi Josh,

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

Thanks very much

[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)

---

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

[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