SQL date format convert? [dd.mm.yy to YYYY-MM-DD]

mysql sql date format sql-function
user2216190 picture user2216190 · Sep 3, 2013 · Viewed 71.1k times · Source

is there mySQL function to convert a date from format dd.mm.yy to YYYY-MM-DD?

for example, 03.09.13 -> 2013-09-03.

Answer

Ed Gibbs picture Ed Gibbs · Sep 3, 2013

Since your input is a string in the form 03.09.13, I'll assume (since today is September 3, 2013) that it's dd.mm.yy. You can convert it to a date using STR_TO_DATE:

STR_TO_DATE(myVal, '%d.%m.%y')

Then you can format it back to a string using DATE_FORMAT:

DATE_FORMAT(STR_TO_DATE(myVal, '%d.%m.%y'), '%Y-%m-%d')

Note that the year is %y (lowercase "y") in STR_TO_DATE and %Y (uppercase "Y") in DATE_FORMAT. The lowercase version is for two-digit years and the uppercase is for four-digit years.

Related questions

MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More
Difference between two dates in MySQL

How to calculate the difference between two dates, in the format YYYY-MM-DD hh: mm: ss and to get the result in seconds or milliseconds?

Read More
Error in MySQL when setting default value for DATE or DATETIME

I'm running MySql Server 5.7.11 and this sentence: updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00' is not working. Giving the error: ERROR 1067 (42000): Invalid default value for 'updated' But the following: updated datetime NOT NULL DEFAULT '1000-01-01 00:00:00' …

Read More