Convert MySQL Datetime to Date Format

datatypesMySQLupdate

Currently I have a table containing a column of type datetime. I have no need for the use of the HH:MM:SS that trail the variables stored in this column. I just need the YYYY-MM-DD. Is there any way to change the column to store just the YY-MM-DD?

If yes, would this corrupt any of the data?

If no, would the solution lie in creating a completely new column?

Best Answer

From the manual here, you can simply use the DATE() function.

 DATE(expr)

Extracts the date part of the date or datetime expression expr.

mysql> SELECT DATE('2003-12-31 01:02:03');
        -> '2003-12-31'

It is designed for precisely your use case. Although the quote is from the 5.7 manual, it works right back to at least 5.0. You don't have to worry about corrupting your data.

If you are using 5.7, you can use a generated column:

The syntax is:

TYPE [ GENERATED ALWAYS ] AS ( ) [ VIRTUAL|STORED ] [ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ]
[ COMMENT ]

MyDate DATE AS (DATE(My_Date_Time_Field))  -- or similar, not tested

This avoids throwing away data which might be useful later and nicely tidies your SQL syntax.

However, if you're using an earlier version of MySQL, you can always generate this DATE() part on the fly, which given the relatively simple syntax, shouldn't be too problematic.

Related Question