0

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.

NotJay
  • 3,919
  • 5
  • 38
  • 62
  • 1
    Back again are we ;), this might help http://stackoverflow.com/a/4794081/1596244 – AnotherUser Jun 25 '14 at 14:38
  • What is the format of your startdate and enddate columns? – Evan Volgas Jun 25 '14 at 14:38
  • 2
    How is your current query not working? – Andrew Jun 25 '14 at 14:38
  • The query above should work. – Satwik Nadkarny Jun 25 '14 at 14:39
  • @Andrew because some of the start dates are the specified date or earlier and some of the end dates are the specified date or later. I know the query is wrong. – NotJay Jun 25 '14 at 14:40
  • Do you mean something like ` between STARTDATE and ENDDATE'? – Andrew Jun 25 '14 at 14:42
  • 3
    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 Jun 25 '14 at 14:42
  • @MarkD Yes, you're right. I forgot to change it back after testing it the other way, the query i provided still will not do what I want though. – NotJay Jun 25 '14 at 14:44
  • @AnotherUser Thank you for referring me to that question. However, I am unable to get any of those answers to work properly. – NotJay Jun 25 '14 at 15:04
  • why do you have startdate and enddate in quotes? I'm surprised you aren't getting errors from that query. – Jenn Jun 25 '14 at 17:12
  • https://stackoverflow.com/questions/29690079/how-to-select-rows-if-given-date-is-between-two-dates open the above link for answer – 6174 Apr 01 '22 at 08:27

1 Answers1

0

Use the DATEDIFF function with a datepart of day.

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

Source

Community
  • 1
  • 1
berg37
  • 177
  • 9