2

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
----------------
|      |       |
----------------
rjbogz
  • 810
  • 1
  • 15
  • 32

1 Answers1

-1

Try CONVERT(DATE, GETDATE()) OR CONVERT(VARCHAR, GETDATE(), 22)

Devs
  • 33
  • 8