CODEWITHSUNDEEP
mysqlsql
Ing. Michal Hudak
Ing. Michal Hudak

Reputation: 5612

SQL get current date with specific time

I am trying to get orders which are approved between 8AM and 4PM

SELECT 
    * 
FROM 
    orders 
WHERE 
    approved_date BETWEEN 'CURDATE() 8:00:00' AND 'CURDATE() 16:00:00'

Here I am trying to get orders which are approved after 4PM (till next working day 8AM)

SELECT 
    * 
FROM 
    orders 
WHERE 
    approved_date BETWEEN 'CURDATE() 16:00:00' AND 'NEXT_WORKING_DAY 8:00:00'

How to write these queries correctly?

approved_date is DATETIME format (0000-00-00 00:00:00) using MySQL

Edit: working day = monday tuesday wednesday thursday friday

Upvotes: 0

Views: 1473

Answers (3)

user3612020
user3612020

Reputation:

Use it as this:

timestampadd(HOUR, 16, CURDATE()) # same day 16:00

timestampadd(HOUR, 32, CURDATE()) # next day 8:00

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

How about this?

 WHERE date(approved_date) = CURDATE() AND
       time(approved_date) BETWEEN '8:00:00' AND '16:00:00'

I don't understand what the "NEXT_WORKING_DAY" is supposed to mean. Perhaps it is this:

WHERE date(approved_date) = CURDATE() AND time(approved_date) >= '8:00:00' OR
      date(approved_date = CURDATE() + 1 AND time(approved_date) <= '16:00:00'

Upvotes: 0

Robert Christie
Robert Christie

Reputation: 20685

Use timestampadd

timestampadd(HOUR, 16, CURDATE()) # 4 pm
timestampadd(HOUR, 32, CURDATE()) # 8 am the next day

Upvotes: 1

Related Questions