CODEWITHSUNDEEP
sqldate
user1505054
user1505054

Reputation: 107

SQL Current and Future Dates

I have an Events table and have found a solution for selecting all Current events and Upcoming events (within the next 14 days).

I'm just wondering if there is a better solution than using the OR in the WHERE statement. This solution makes me feel like a crappy programmer.

SELECT
   eventID
  ,eventTitle
  ,eventStartDate
  ,eventFinishDate
FROM Events
WHERE eventStartDate <= GETDATE() AND eventFinishDate >= GETDATE()
OR eventStartDate >= GETDATE() AND DATEDIFF(DAY,GETDATE(),eventStartDate) <= 14
ORDER BY eventStartDate

Your wisdom is greatly appreciated! Thanks much

Upvotes: 3

Views: 871

Answers (2)

Luc
Luc

Reputation: 319

You probably need something like:

SELECT
   eventID
  ,eventTitle
  ,eventStartDate
  ,eventFinishDate
FROM Events
WHERE GETDATE() BETWEEN eventStartDate AND eventFinishDate
OR eventStartDate BETWEEN GETDATE() AND DATEADD(DAY, 14, GETDATE())
ORDER BY eventStartDate

By the way, always take care when using OR operators to specify exactly what you need using parenthesis. Your code example might fail because of this. If you were getting wrong results, you should try:

SELECT
   eventID
  ,eventTitle
  ,eventStartDate
  ,eventFinishDate
FROM Events
WHERE (eventStartDate <= GETDATE() AND eventFinishDate >= GETDATE())
OR (eventStartDate >= GETDATE() AND DATEDIFF(DAY,GETDATE(),eventStartDate) <= 14)
ORDER BY eventStartDate

In my code this it not needed because it's not a complex expression, there's just one OR.

Upvotes: 2

Andomar
Andomar

Reputation: 238196

Assuming eventStartDate <= eventFinishDate, you could just say it has to start before 2 weeks from now, and end after today:

where   eventStartDate <= dateadd(week, 2, getdate())
        and getdate() <= eventFinishDate

Upvotes: 2

Related Questions