Mysql str_to_date shows null instead of formatted date

Question

I am facing a silly problem while converting datetime column into y-m-d format.

I am running following query :

SELECT STR_TO_DATE(dateadded, '%y-%m-%d') FROM my_table

Note : The dateadded column is a type of datetime.

Whenever I am running above query it always shows (NULL).

Can somebody solve this issue as this is irritating me ?

Answer 1

Since your column is of datetime type rather than string type you should use DATE_FORMAT function as below:

SELECT DATE_FORMAT(dateadded, '%y-%m-%d') 
FROM my_table

Answer 2

This query will work for a four digit year in table

SELECT STR_TO_DATE(dateadded, '%Y-%m-%d') 
FROM my_table

while this will work for a two digit year

SELECT STR_TO_DATE(dateadded, '%y-%m-%d') 
FROM my_table

you can try this by following query

SELECT 
   STR_TO_DATE('99-12-12', '%y-%m-%dd'),
   STR_TO_DATE('1999-12-12', '%Y-%m-%dd')

both output will be

1999-12-12

for further details