CODEWITHSUNDEEP
mysqlstr-to-date
Brendan O'Sullivan
Brendan O'Sullivan

Reputation: 35

Convert CHAR to date in mysql

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.

Upvotes: 0

Views: 859

Answers (1)

user149341
user149341

Reputation:

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)

Upvotes: 1

Related Questions