MySQL Select rows where specified date is between start date and end date

0
votes

My table has a column (datetime) for start date and time, "startdate", and a column (datetime) for end date and time, "endtime".

I want to select rows from the table where the startdate and enddate include a specified date.

Example: I have a row that has a startdate of 2014-06-22 20:00:00 and an enddate of 2014-06-29 23:00:00 and I want to select that row with the specified date range for a given day (2014-06-25).

Here is my query that is not currently doing what I want it to...

SELECT * FROM my_tbl WHERE `startdate` >= '2014-06-22 00:00:00' AND `enddate` <= '2014-06-22 23:59:59'

I have also tried the Between in the SQL query. I think I am going to have to create a date range with the startdate and enddate and reference that from the specified date but I don't know how to do that. Please help!

EDIT:

My table has 2 date columns. I am injecting a specific day (2014-06-25). I want to return rows that have a start date (startdate) before or on the specified day and an end date (enddate) after or on the specified date. So basically I want active rows to show up based on a specified day.

1
mysqlsqldatetime
Back again are we ;), this might help stackoverflow.com/a/4794081/1596244 – AnotherUser
What is the format of your startdate and enddate columns? – Evan Volgas
How is your current query not working? – Andrew
The query above should work. – Satwik Nadkarny
shouldn't it be startdate <= and end date>= if your specified date is to be between the two. you are currently looking for rows that start after specified date and end before specified date which is probably impossible – MarkD

1 Answers

0
votes

Use the DATEDIFF function with a datepart of day.

SELECT * FROM my_table WHERE DATEDIFF(day, date1, date2) >= 0

Source