MySQL STR_TO_DATE hour formatting

Question

I'm trying to format a string containing a time-value with the STR_TO_DATE function. The time value is : hour(0-23) without leading zero + minute (00-59) e.g. 800, 1245 or 1535. To format these values I tried to use the %k hour specifier as described in the MySQL5.5 documentation, but this does not work if the hour is without a leading zero:

Does not work:

+-----------------------------------+
| SELECT STR_TO_DATE("800","%k%i"); |
+-----------------------------------+
| NULL                              |
+-----------------------------------+

Works as expected:

+------------------------------------+
| SELECT STR_TO_DATE("1855","%k%i"); |
+------------------------------------+
| 18:55:00                           |
+------------------------------------+

+-----------------------------------+
| SELECT STR_TO_DATE("0800","%k%i");|
+-----------------------------------+
| 08:00:00                          |
+-----------------------------------+

I'm working on MySQL 5.5 and double checked the statements (on console and in web-gui) and they give the above (wrong) output — Michael, Apr 30 '17 at 15:33

score 1 · Answer 1 · answered Apr 30 '17 at 16:04

1

You can use LPAD() to add a leading zero:

SELECT STR_TO_DATE(LPAD("800", 4, 0), "%k%i");

answered Apr 30 '17 at 16:04

Paul Spiegel

30,925
5
44
53

MySQL STR_TO_DATE hour formatting

1 Answers1