topic Re: Definition Query with start and end dates in ArcMap Questions

Definition Query with start and end dates

MichaelScott2 — Mon, 26 Apr 2021 14:21:58 GMT

I have data in an SQL database that has fields for a start date (StartDate) and end date (EndDate).

My client would like the features to be visible only when the current date is between the start date and 45 day after the end date.

I have tried many different definition query iterations and looked at a lot of forum posts. The closest I have gotten to a working definition query is...

GetDate() >= StartDate AND EndDate <= GetDate() - 45

I'd like the second part to be GetDate() <= EndDate + 45, but that gives me a syntax error.

I can't seem to wrap my brain around the logic for this definition query.

Any assistance would be greatly appreciated.

Thank you.

Re: Definition Query with start and end dates

JoshuaBixby — Mon, 26 Apr 2021 14:44:04 GMT

There is a fair amount of nuance/difference in how different DMBSs implement Date/Time SQL support. Which back-end DBMS are you working with?

Re: Definition Query with start and end dates

MichaelScott2 — Mon, 26 Apr 2021 14:55:19 GMT

We are using SQL Server 2017.

Re: Definition Query with start and end dates

JoshuaBixby — Mon, 26 Apr 2021 17:58:26 GMT

Try:

GETDATE() BETWEEN StartDate and DATEADD(day, 45, EndDate)

GETDATE() >= StartDate AND GETDATE() <= DATEADD(day, 45, EndDate)

Re: Definition Query with start and end dates

MichaelScott2 — Mon, 26 Apr 2021 18:32:28 GMT

Both solutions work.

Thank you so much.