mysql convert String to datetime format

Question

In MySQL trying to convert string to date time format, but its returning the null value.

What it the correct way to convert below string to datetime format?

> select STR_TO_DATE('8/16/18 7:00 PM', '%c/%e/%Y %r');
+-------------------------------------------------+
| STR_TO_DATE('8/16/18 7:00 PM', '%c/%e/%Y %r')   |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+

Thanks

Erubiel · Accepted Answer · 2018-08-27 15:50:55Z

1

You gotta match the format, %r doesn't match 7:00 PM

From: https://www.w3schools.com/sql/func_mysql_str_to_date.asp

%r = Time in 12 hour AM or PM format (hh:mm:ss AM/PM)

As you can see, expects a two digit hour, and seconds, which you don't have.

You also don't match the year %Y expects a four digit Year... so i changed it to %y

Try like this.

select STR_TO_DATE('8/16/18 7:00 PM', '%c/%e/%y %l:%i %p');

edited Aug 27, 2018 at 15:50

answered Aug 27, 2018 at 15:37

Erubiel

2,97216 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql convert String to datetime format

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related