0

how to select data between 2 dates in between 2 dates? for example :

start_date - end_date -- title

2014-01-28 2014-02-03 test

2014-02-01 2014-02-15 ests

2014-02-28 2014-03-03 sets

2014-03-01 2014-03-10 sste

the problem is, i want to select data between 2014-02-02 and 2014-02-28 with first three of them selected because the first three data is included on 2nd month.

i tried this, but not works

SELECT title FROM my_tables WHERE start_date BETWEEN 2014-02-02 AND 2014-02-28

how it works?

Improve this question

3 Answers 3

Reset to default
2

Two periods overlap when one begins before the other ends, and the other ends after the first begins. Here is the correct logic:

SELECT title
FROM my_tables
WHERE start_date <= '2014-02-28' and
      end_date >= '2014-02-02';

Note that date constants need to be in single quotes.

Here is a SQL Fiddle showing it working.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

have you tried that? i tried that, but it not works, no data displayed :(
@MuhammadZaidTaufiq . . . It works. Check the SQL Fiddle in the answer.
i don't understand about the schema in your fiddle, i think it's not same as my_table above
ehmm, sorry bro, that's my fault :) it's worked! THANKS!
0

Add single quote to date constant

SELECT title FROM my_tables WHERE start_date BETWEEN '2014-02-02' AND '2014-02-28'
Improve this answer

4 Comments

i'm sorry, i forget it but it still not works, any other advice?
it's not error, but it's not the output what i want, i want the first three data as the output
what is datatype of start_date?
the datatype is date bro
0

A date range overlaps with another when either the start or the end is within that other range:

given range:    |----|
range 1     |----|           overlaps with given range, because end_date is in that range
range 2            |----|    overlaps with given range, because start_date is in that range
range 3  |----|              does not overlap with given range, because both start and end date are not in that range

SELECT title FROM my_tables 
WHERE (start_date BETWEEN '2014-02-02' AND '2014-02-28')
OR (end_date BETWEEN '2014-02-02' AND '2014-02-28');
Improve this answer

3 Comments

almost! but the output is duplicate if i use that, how?
There must be duplicate titles in my_tables. You can of course select distinct titles by using SELECT DISTINCT title from ...
I just notice there should be parentheses used. I'll update my answer.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.