CODEWITHSUNDEEP
mysqldatetimetime
KleverKrypto
KleverKrypto

Reputation: 518

MySQL convert time to HH:MM

Why does SELECT STR_TO_DATE('07:30 PM','%H:%I %p'); return NULL?

I'd expect to see 19:30

More Context:

I'm trying to query a table where the time column values are stored as HH:MM PM. Example:

SELECT * FROM table where (STR_TO_DATE(table.time, '%H:%I %p') < NOW())

Upvotes: 0

Views: 3222

Answers (2)

user7158855
user7158855

Reputation:

NOW() returns a datetime, something like 2014-11-22 12:45:34. You probably want to be comparing times, not dates as you aren't comparing the actual day, just the time. Try using CURTIME() instead. Additionally, %I is for hours, you want %ifor minutes. And %H is for a 12 hour format, you want %h for a 24 hour format. So your statement should be more like:

SELECT * FROM table where (STR_TO_DATE(table.time, '%h:%i %p') < CURTIME())

For future reference, here's a table of format codes from the MySql docs: enter image description here

See the original documentation here:
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Upvotes: 1

&#193;lvaro Gonz&#225;lez
&#193;lvaro Gonz&#225;lez

Reputation: 146660

You've mangled some format codes. It should be '07:30 PM','%h:%i %p'. Additionally, you need a SQL mode that allows invalid dates because of this:

Unspecified date or time parts have a value of 0, so incompletely specified values in str produce a result with some or all parts set to 0

mysql> SET @@SESSION.sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    -> STR_TO_DATE('07:30 PM','%H:%I %p') as original,
    -> STR_TO_DATE('07:30 PM','%h:%i %p') as fixed;
+----------+-------+
| original | fixed |
+----------+-------+
| NULL     | NULL  |
+----------+-------+
1 row in set, 3 warnings (0.00 sec)

mysql> SET @@SESSION.sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    -> STR_TO_DATE('07:30 PM','%H:%I %p') as original,
    -> STR_TO_DATE('07:30 PM','%h:%i %p') as fixed;
+----------+----------+
| original | fixed    |
+----------+----------+
| NULL     | 19:30:00 |
+----------+----------+
1 row in set, 1 warning (0.00 sec)

Upvotes: 1

Related Questions