megacolorboy

Back

Published on March 12th, 2021

MySQL

Convert string to date using MySQL

Today, I was debugging a piece of code that is supposed to return a list of data based on the year, the funny thing is that the data was being returned on the development server but not on the production one.Weird, so I opened up MySQL Workbench and wrote a simple query to see if the dates were being returned because who knows maybe they weren't stored at all.The values returned were . Now, that's strange because the dates were present in the column. So, I took a deep look and figured out that the dates were stored in instead of data type! 😔Luckily, I figured that there's a way to resolve this by function:Bam! The results were coming now! 😌Hope this helps you out!

1 minute read

Today, I was debugging a piece of code that is supposed to return a list of data based on the year, the funny thing is that the data was being returned on the development server but not on the production one.

Weird, so I opened up MySQL Workbench and wrote a simple query to see if the dates were being returned because who knows maybe they weren't stored at all.


SELECT YEAR(date_posted) FROM posts;

The values returned were null. Now, that's strange because the dates were present in the column. So, I took a deep look and figured out that the dates were stored in VARCHAR instead of DATETIME data type! 😔

Luckily, I figured that there's a way to resolve this by STR_TO_DATE() function:


SELECT YEAR(STR_TO_DATE(date_posted, '%Y-%m-%d')) FROM posts;

Bam! The results were coming now! 😌

Hope this helps you out!