Null Values Using STR_TO_DATE() MySQL

Question

For whatever reason, certain dates return null when using STR_TO_DATE() whereas others don't.

CREATE TABLE persons (
personid int,
name varchar(100),
dob date;

insert into persons values 
(5,"Gov","1985-04-23"),
(1,"Gov","1993-04-23"), 
(2,"Sam","1991-11-19"),
(3,"A","1993-04-23"),
(4,"B","1991-11-19");

SELECT STR_TO_DATE( CONCAT(  MONTH(DOB), DAY(DOB), YEAR(CURDATE()) ), '%m %d %Y') FROM persons;

RETURNS

STR_TO_DATE( CONCAT( MONTH(DOB), DAY(DOB), YEAR(CURDATE()) ), '%m %d %Y')
--------------------------------------------------------------------------
(null)
November, 19 2016 00:00:00
(null)
November, 19 2016 00:00:00
(null)

Please see fiddle: http://sqlfiddle.com/#!9/a98eef/1/0

I wonder more that it works for dates without zero-filled values. BTW: `dob + INTERVAL (YEAR(CURDATE()) - YEAR(dob)) YEAR` is doing the same (except of Feb. 29th). — Paul Spiegel, Aug 25 '16 at 22:40

score 0 · Answer 1 · answered Aug 25 '16 at 21:55

Your format string says there's a space after the month and day. But you're not putting any spaces in when you concatenate them. Use CONCAT_WS to specify a separator between the items being concatenated.

STR_TO_DATE( CONCAT_WS(' ', MONTH(DOB), DAY(DOB), YEAR(CURDATE()) ), '%m %d %Y')

corrected fiddle

Null Values Using STR_TO_DATE() MySQL

1 Answers1