Definition Query with start and end dates

682
4
Jump to solution
04-26-2021 07:21 AM
Labels (3)
MichaelScott2
New Contributor II

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.

Tags (3)
0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

Try:

 

 

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

 

 

or

 

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

 

View solution in original post

0 Kudos
4 Replies
JoshuaBixby
MVP Esteemed Contributor

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?

0 Kudos
MichaelScott2
New Contributor II

We are using SQL Server 2017.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Try:

 

 

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

 

 

or

 

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

 

0 Kudos
MichaelScott2
New Contributor II

Both solutions work.

Thank you so much. 

0 Kudos