CODEWITHSUNDEEP
mysqlevents
Tim
Tim

Reputation: 3131

SQL to display an event on start date, end date and any days in between

I have an event in my database with a startDate and an endDate.

I need to display this event (based on the current date) on every day the event occurs.

So if the event starts on the 3rd of May and finishes on the 7th of May, the SQL query must find it on every single day.

How can I achieve this?

SELECT * FROM events WHERE startDate ???

Upvotes: 0

Views: 2021

Answers (2)

EdgarVerona
EdgarVerona

Reputation: 1588

Oh, try:

SELECT * FROM events WHERE GetDate() BETWEEN startDate AND endDate

Essentially, you're telling the database to look up the current date (with the GetDate() function) and see if it lies between the start and end date of the given events!

Just to elaborate a bit, one of the things I remember in school that got to me was the use of functions in queries. SQL has a lot of built in functions like GetDate() that you can use pretty much anywhere in your query: in the WHERE clause to compare against values in the given query, in the SELECT clause if you need to do something to data that you want to output, etc... don't be afraid to use 'em! =)

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

Assuming SQL Server:

SELECT * 
FROM events 
WHERE getdate() between startDate and endDate

Upvotes: 3

Related Questions