Convert CHAR to date in mysql

Question

I am trying to compare a year stored as CHAR(4) to a year stored as DATETIME, but every time I try to convert the CHAR year to a date, I always get NULL when I expect the year in format 'YYYY'. I've read the formatting for using str_to_date, but I'm not sure why it always comes out as null (maybe I've misread something). Must the string/format in the str_to_date function be full dates and not just a year format?

SELECT id, STR_TO_DATE(year, '%Y') AS Year
FROM elite_years
LIMIT 100;

Most if not all the data in the elite_years table is a four character year (e.g. '2008'). I can't modify the datatypes in the table either. I am using the Yelp Dataset fyi.

I checked out this post, but from what I could see, I am following the correct formatting which I based off of the docs.

Do it the other way round? convert the char to int and compare with YEAR(dt_fld) — Alex K., Apr 04 '18 at 15:05
Thanks, that worked, but do you have any idea as to why my previous method wasn't working? — Brendan O'Sullivan, Apr 04 '18 at 15:33
I tried it with ' , ` , and ". Only the backtick returns error and the other two bring NULL. I typed it wrong here, I had just a single quote in my code — Brendan O'Sullivan, Apr 04 '18 at 16:41
Possible duplicate of [how to convert a string to date in mysql?](https://stackoverflow.com/questions/5201383/how-to-convert-a-string-to-date-in-mysql) — ivan_pozdeev, Apr 04 '18 at 16:43
@Roy, I've already found an alternate solution thanks to Alex K., I'm just looking for a rational as to why it would keep returning NULL. — Brendan O'Sullivan, Apr 04 '18 at 18:39
@ivan_pozdeev, no. That question is asking for the existence of a str_to_date function, which I am using. My question is trying to understand why the function could return NULL when I am following the correct format (but probably not somehow). — Brendan O'Sullivan, Apr 04 '18 at 18:41

score 1 · Accepted Answer · answered Apr 04 '18 at 18:45

To parse a string using STR_TO_DATE(), there needs to be enough information in the string to uniquely identify a date. A year alone isn't enough -- you still need a month and day, in some form or another.

This is hinted at in the warning you get when you try:

mysql> SELECT STR_TO_DATE('2009', '%Y') \G
*************************** 1. row ***************************
str_to_date('2009', '%Y'): NULL
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: Warning
   Code: 1411
Message: Incorrect datetime value: '2009' for function str_to_date
1 row in set (0.00 sec)

Convert CHAR to date in mysql

1 Answers1