Find result with specific DateTime range

Question

0.00/5 (No votes)

See more:

Hi,
My table

----------------------------------------------------------
Reservation ID | StartDateTime       | EndDateTime
00001          | 2013-06-01 01:00:00 | 2013-06-01 03:00:00
00002          | 2013-06-01 02:00:00 | 2013-06-01 05:00:00
00003          | 2013-06-01 03:00:00 | 2013-06-01 06:00:00
00004          | 2013-06-04 06:00:00 | 2013-06-01 07:00:00
00004          | 2013-06-04 09:00:00 | 2013-06-01 11:00:00

-
-
-

I try to find record from 2013-06-01 02:00:00 to 2013-06-01 03:00:00. With the following

SQL

SELECT *
FROM reservation
WHERE((startDateTime BETWEEN '2013-06-01 02:00:00' AND '2013-06-01 03:00:00') OR
(endDateTime BETWEEN '2013-06-01 02:00:00' AND '2013-06-01 03:00:00') OR
('2013-06-01 02:00:00' BETWEEN startDateTime AND endDateTime) OR
('2013-06-01 03:00:00' BETWEEN startDateTime AND endDateTime));

The result

----------------------------------------------------------
Reservation ID | StartDateTime       | EndDateTime
00001          | 2013-06-01 01:00:00 | 2013-06-01 03:00:00
00002          | 2013-06-01 02:00:00 | 2013-06-01 05:00:00
00003          | 2013-06-01 03:00:00 | 2013-06-01 06:00:00 <--- wrong

I don't want the 03:00:00 because it's out of my time range. How to omit the 03:00:00. It's hard to explain. I want reservation ID of 02:00:00 to 03:00:00 but it gave me extra one. Although the result is correct and work as function but ....sigh. idk.

Posted 4-Jun-13 6:40am

Valentine1993

Updated 4-Jun-13 7:22am

Maciej Los

v2

Add a Solution

Comments

Mohibur Rashid 5-Jun-13 5:18am

id 4 came twice, ignorable as typo, both 4 start date is 2013-06-04 and end on 2013-06-01

Valentine1993 6-Jun-13 3:06am

ya typo. Srry

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-06-04T07:31:00

It's simple.

SQL

SELECT *
FROM reservation
WHERE (startDateTime >='2013-06-01 02:00:00') AND (endDateTime <='2013-06-01 03:00:00'

Above query returns 0 records.

SQL

SELECT *
FROM reservation
WHERE (startDateTime BETWEEN '2013-06-01 02:00:00'  AND '2013-06-01 03:00:00') AND (endDateTime BETWEEN '2013-06-01 02:00:00'  AND '2013-06-01 03:00:00')

Above query returns 0 records.Test it with OR operator between two conditions:

SQL

SELECT *
FROM reservation
WHERE (startDateTime BETWEEN '2013-06-01 02:00:00'  AND '2013-06-01 03:00:00') OR (endDateTime BETWEEN '2013-06-01 02:00:00'  AND '2013-06-01 03:00:00')

What's the difference?

SQL

SELECT *
FROM reservation
WHERE (startDateTime BETWEEN '2013-06-01 02:00:00'  AND '2013-06-01 03:00:00')

Above query returns ID 00002.

SQL

SELECT *
FROM reservation
WHERE (endDateTime BETWEEN '2013-06-01 02:00:00'  AND '2013-06-01 03:00:00')

Above query returns ID 00001.

Please, read about MySQL logical operators[^].

Valentine1993 · Accepted Answer · 2013-06-04T21:28:00

Solution 2

The ultimate answer.

SQL

Select *
From reservation
Where '2013-06-01 02:00:00' < endDateTime AND '2013-06-01 03:00:00' > startDateTime

Thank you everyone!!!

Posted 4-Jun-13 21:28pm

Valentine1993

Comments

Maciej Los 5-Jun-13 4:21am

GOod job! I would suggest you to mark all answers as "solved" (grenn button) - foramlly.
Have a 5 from me!

Valentine1993 5-Jun-13 5:18am

oh yeah. Hey mark mine as solved? i'm newbie

Maciej Los 5-Jun-13 6:14am

Both ;) Your and mine...

Valentine1993 6-Jun-13 3:05am

(: