mysqlsqldateformatsql-function

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

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.

Solution

  • 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')

    It will give you a standard MySQL date 2013-09-03

    Then if you want it with short year, you can format it using DATE_FORMAT:

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

    It will give you 13-09-03

    Note that the year is %y (lowercase "y") is for two-digit years and the uppercase is for four-digit years.