I have a column called ReleaseDate (which is a string). Most of them are in the form of %e %M %Y
(25 May 1979), but there are a few where it is just %M %Y
(May 1979) or %Y
(1979). Unfortunately, it doesn't seem the STR_TO_DATE
works with just %M %Y
. Here is what I have tried:
SELECT ReleaseDate,
CASE
WHEN STR_TO_DATE(ReleaseDate, '%e %M %Y') IS NULL
THEN CASE
WHEN STR_TO_DATE(ReleaseDate, '%M %Y') IS NULL
THEN STR_TO_DATE(ReleaseDate, 'Y')
ELSE STR_TO_DATE(ReleaseDate, '%M %Y')
END
ELSE STR_TO_DATE(ReleaseDate, '%e %M %Y')
END,
STR_TO_DATE(ReleaseDate, '%M %Y')
FROM MyTable
But it returns the following:
RealeaseDate CASE STR_TO_DATE
-------------------------------------------------
| 20 May 2003 | 5/20/2003 12:00:00 AM | {null} |
| 16 May 2000 | 5/16/2000 12:00:00 AM | {null} |
| May 1976 | {null} | {null} |
-------------------------------------------------
Is there any workaround for this?
EDIT
To add to this, I have attempted to change sql_mode
by taking out STRICT_TRANS_TABLES
and even adding in ALLOW_INVALID_DATES
, but this also didn't work. According to https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date, I was not able to use the following example either:
SELECT STR_TO_DATE('9','%m');
which also returns {null}
.
I have also tried setting sql_mode
to be blank. By running:
SELECT version();
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session
I get:
version() *
-------------
| 5.6.14 |
-------------
global session
----------------
| | |
----------------