Reputation: 794
In one of my projects, I was using the function STR_TO_DATE()
to convert a string into a date value based on the fmt format string. For example to convert a string into a DATE value:
SELECT STR_TO_DATE('21-5-2013','%d-%m-%Y'); // returns 2013-05-21
But when I went to convert a time string into a TIME value, I get some strange behaviors:
SELECT STR_TO_DATE('203005','%h%i%s'); // returns null
SELECT STR_TO_DATE('19:30:05','%h:%i:%s'); // returns null
SELECT STR_TO_DATE('113005','%h%i%s'); // returns 11:30:05
SELECT STR_TO_DATE('01:30:05','%h:%i:%s'); // returns 01:30:05
One of the answers I found here in StackOverflow is that:
This is indeed because of the SQL mode NO_ZERO_DATE.
But I'm still intrigued by the behavior of the function. You could check easily what I'm talking about by following this link
Upvotes: 1
Views: 2564
Reputation: 847
I ended up contat-ing the date doing something like this to avoid having a null
.
SELECT STR_TO_DATE(
concat('01/01/2018', ' ', '19:30:05'),
'%d/%m/%Y %H:%i:%s'
),
'%H:%i:%s'
);
Upvotes: 1
Reputation: 372
The problem is the format you are setting, it recognises the time from 0-12, not 0-23. It should be
SELECT STR_TO_DATE('203005','%H%i%s');
SELECT STR_TO_DATE('19:30:05','%H:%i:%s');
Check the following link with the list of all available time and date formats. https://www.techonthenet.com/mysql/functions/str_to_date.php
Upvotes: 4