CODEWITHSUNDEEP
mysqlsql
Ayiko Nishimura
Ayiko Nishimura

Reputation: 81

Search between two dates with specific time with each date

I have two dates. 2019-01-01(fromDate) and 2019-01-10(toDate). And now I want to search only the 13:00 to 16:00 time of each date. May I ask if is it possible using query only? Any answer is much appreciated

SELECT * 
FROM table 
WHERE fromDate >= 2019-01-01 AND toDate <= 2019-01-10

Upvotes: 8

Views: 165

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

Use separate conditions on the date and on the time:

SELECT * 
FROM table 
WHERE fromDate >= '2019-01-01' AND
      toDate < '2019-01-11' and
      time(fromdate) between '13:00:00' and '16:00:00' and
      time(todate) between '13:00:00' and '16:00:00;

Or, if you don't want 16:00:00 exactly, you can use hour():

SELECT * 
FROM table 
WHERE fromDate >= '2019-01-01' AND
      toDate < '2019-01-11' and
      hour(fromdate) in (13, 14, 15) and
      hour(todate) in (13, 14, 15)

Upvotes: 3

D-Shih
D-Shih

Reputation: 46219

You can try to use STR_TO_DATE function with the format and HOUR function.

SELECT * 
FROM table 
WHERE 
    fromDate >= STR_TO_DATE('2019-01-01', '%Y-%m-%d') 
AND 
    toDate <= STR_TO_DATE('2019-01-10', '%Y-%m-%d')
AND
    (HOUR(fromDate) BETWEEN 13 AND 16 OR HOUR(toDate) BETWEEN 13 AND 16)

Upvotes: 5

Related Questions