CODEWITHSUNDEEP
mysqlsqlstr-to-date
Ankit
Ankit

Reputation: 657

Mysql str_to_date shows null instead of formatted date

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 ?

Upvotes: 0

Views: 269

Answers (2)

Zahid Ali
Zahid Ali

Reputation: 466

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

Upvotes: 0

SMA
SMA

Reputation: 37103

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

Upvotes: 2

Related Questions