CODEWITHSUNDEEP
mysqldatetimetime
Ricardo Faria
Ricardo Faria

Reputation: 794

STR_TO_DATE() returning NULL for some TIME values

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: 2565

Answers (2)

awavi
awavi

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

Vasiliki M.
Vasiliki M.

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

Related Questions