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

Question

Hello,

This should be fairly simple, but I can't get my head around it.

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 ???

Thanks,

Tim

Answer 1

Assuming SQL Server:

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

Answer 2

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! =)