CODEWITHSUNDEEP
mysqlsqldatetimewhere-clause
Ricardo Carrillo
Ricardo Carrillo

Reputation: 19

Results between different hours and same day SQL

I need your help, I have this.

AND Fecha BETWEEN '2020-05-21 06:00:00' AND '2020-05-21 15:30:00'

and it's working,,, but I want to use something like this

AND Fecha BETWEEN Fecha '06:00:00' AND Fecha '15:30:00'

And doesn't work,,, can you help me? I want to have the restult between diferent hour but same day, using Fecha.

enter image description here

enter image description here

For you help, Thanks

Upvotes: 1

Views: 236

Answers (3)

forpas
forpas

Reputation: 164089

If you want the results for the current date:

WHERE DATE(FETCHA) = CURRENT_DATE AND TIME(FETCHA) BETWEEN '06:00:00' AND '15:30:00'

or for any other date:

WHERE DATE(FETCHA) = ? AND TIME(FETCHA) BETWEEN '06:00:00' AND '15:30:00'

replace ? with the date that you want the results.

Upvotes: 1

O. Jones
O. Jones

Reputation: 108641

I guess you want records from all calendar dates with times between 06:00 and 15:30.

Try this

AND TIME(Fecha) BETWEEN '06:00:00' AND '15:30:00'

If you compare 00:06:00 directly to a DATETIME or TIMESTAMP column, you actually compare 0000-00-00 06:00:00. That was long ago.

Upvotes: 1

GMB
GMB

Reputation: 222432

You can use time() to extract the time portion of a datetime or timestamp, and then use in for comparison:

and time(fecha) between '06:00:00' and '15:30:00'

Note: I am unsure that you want to include the upper bound; presumably, you could use an half-open interval instead:

and time(fecha) >= '06:00:00' and time(fecha) < '15:30:00'

Upvotes: 2

Related Questions