CODEWITHSUNDEEP
mysqldatedatetimebetween
Scorpion
Scorpion

Reputation: 6891

How to get data from date and time range in MySQL

MySQL Table: -

ID    |    From_DateTime      |    To_DateTime

1          2014-09-01 10:00:00    2014-09-10 22:00:00

Explanation:

I have added 2 columns in database table for Date and Time range. The data shown above means that from 01-09-2014 to 10-09-2014 is the date range and 10:00:00 to 22:00:00 is the time range.

Positive Scenario - Now I am passing 2014-09-05 15:00:00 in my query which comes into Date and Time both range. What I need is the ID from the query.

Negative Scenario - Now I am passing 2014-09-05 23:00:00 in my query which comes into Date comes into range but TIME is not in the specified range so I should get 0 result.

I have no idea about database queries and that is why I am posting it to here to get some help from the database experts.

Upvotes: 1

Views: 988

Answers (2)

Chris Caviness
Chris Caviness

Reputation: 586

Something like this should work. May need some tweaking.

SELECT ID FROM (
  SELECT ID FROM <tablename> 
    WHERE DATE('<dateTimeValue>') BETWEEN DATE(From_DateTime) AND DATE(To_DateTime) 
    AND TIME('<dateTimeValue>') BETWEEN TIME(From_DateTime) AND TIME(To_DateTime)
  UNION
    SELECT 0 AS ID FROM DUAL
) AS a LIMIT 1

Upvotes: 1

Haseena P A
Haseena P A

Reputation: 17376

SELECT id FROM date_time WHERE From_DateTime < '2014-09-05 15:00:00' AND To_DateTime > '2014-09-05 15:00:00';

here date_time is table name... Don't forgot to put date in single quotes

Upvotes: 0

Related Questions