Definition Query with dates

585
3
Jump to solution
07-01-2021 05:35 AM
Labels (1)
GregorCalderwood1
New Contributor

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

 

Tags (3)
1 Solution

Accepted Solutions
jcarlson
MVP Notable Contributor

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)

 

- Josh Carlson
Kendall County GIS

View solution in original post

3 Replies
jcarlson
MVP Notable Contributor

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)

 

- Josh Carlson
Kendall County GIS
GregorCalderwood1
New Contributor

Hi Josh,

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

 

Thanks very much

 

 

 

0 Kudos
Prab
by
New Contributor
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');


0 Kudos