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

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