CODEWITHSUNDEEP
mysqlsql
user1191027
user1191027

Reputation:

NULL when converting VARCHAR to DATETIME

When I use the following statement I just get NULL back where it's supposed to return values and undefined fields where the column value is blank.

SELECT STR_TO_DATE(strdate, '%Y-%m-%d %H:%m:%s') from table

enter image description here

My date format is set to 2013-12-11 00:58:00.

If I select all without converting it (just displaying the VARCHAR as is) then I get this:

enter image description here

Upvotes: 0

Views: 929

Answers (3)

gvee
gvee

Reputation: 17161

From the manual

If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning.

What this means is that the value of strdate does not match the format pattern of '%Y-%m-%d %H:%m:%s' (also note the format string needs to follow the same set up and rules listed under DATE_FORMAT in the manual)

You should always store date[time] values in their appropriate data types. A string that looks like a date is still a string and NOT a date.

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65264

The culprit is '%Y-%m-%d %H:%m:%s' - %m can't stand for month and minute. IIRC %m is correct for them month, and %i is what you want for the minute.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

The correct code for minutes is %i not %m:

SELECT STR_TO_DATE(strdate, '%Y-%m-%d %H:%i:%s')
from (select '2013-12-11 00:58:00' as strdate) t

Upvotes: 1

Related Questions