mysqlsqlstr-to-dateerror-messaging

SQL STR_TO_DATE

I'm having trouble with the following code:

INSERT into `fun` ( funner)
SELECT YEAR(STR_TO_DATE(SUBSTRING(time,1,4), '%Y')) 
FROM `orig`

returning the warning:

Incorrect datetime value: '1880' for function str_to_date

time is a varchar column in the table orig with the format yyyy/mm.

I want to extract the year section from this varchar and translate it into a year datatype using STR_TO_DATE

Solution

  • I would recommend using one of the usual date and time MySQL datatypes, instead of the rarely used YEAR datatype : DATE, DATETIME and TIMESTAMP.

    If you want to turn your string to a date datatype, then :

    STR_TO_DATE(my_column, '%Y/%m')

    You can use the YEAR() function on this date, and it will return an integer value :

    YEAR(STR_TO_DATE(my_column, '%Y/%m'))

    Finally : if all you want is get the year from a date stored as string, then you can directly extract it the string using SUBSTR :

    SUBSTR(my_column, 1, 4)

    This returns a string (not an integer), that MySQL will implictely convert to a number when used in numeric context.