MySQL. How to get o'clock time from hour

Question

I have a time datatype field "MyTimeField" in MySQL. So for example the value can be 10:30:00

I want to select the "o'clock time" in time format, so the output will 10:00:00

tried

 TIME_FORMAT(hour(MyTimeField),'%T')

but no luck, this returns 00:00:10

Any ideas?

https://stackoverflow.com/questions/9680144/mysql-date-time-round-to-nearest-hour will probably steer you in the right direction. — ceejayoz, Feb 05 '18 at 16:15
Possible duplicate of [MYSQL Date Time Round To Nearest Hour](https://stackoverflow.com/questions/9680144/mysql-date-time-round-to-nearest-hour) — PM 77-1, Feb 05 '18 at 16:16
close, but I don't have date as the other post. I only have a time field — Jack Casas, Feb 05 '18 at 16:24

ScaisEdge · Accepted Answer · 2018-02-05T16:33:25.150

1

If you want only the hour you could use

TIME_FORMAT(MyTimeField,'%H:00:00')

edited Feb 05 '18 at 16:33

answered Feb 05 '18 at 16:15

ScaisEdge

this returns 00:00:00 all the time – Jack Casas Feb 05 '18 at 16:20
1

ditch the `HOUR` function; use the raw column as the first argument in the TIME_FORMAT function – spencer7593 Feb 05 '18 at 16:26
TIME_FORMAT(MyTimeField,'%H:00:00') does the job! – Jack Casas Feb 05 '18 at 16:27

1 Answers1