Converting fields to date gives datetime values-CodePudding

Context:

MariaDB 5.5.68.

I have to convert VARCHAR fields containing dates into regular formatted date (as

The conversion is ok but FORMATED_DATE field is more like a DATETIME to me than a DATE, according to documentation. Is that normal because i don't want to deal with the time part of these dates in the application using this field.

CodePudding user response：

Did you try like this?

ALTER TABLE MYTABLE
ADD FORMATED_DATE DATE;

UPDATE MYTABLE
SET FORMATED_DATE = DATE_FORMAT(UNFORMATED_DATE, '%d/%m/%Y')

SELECT FORMATED_DATE, UNFORMATED_DATE  
FROM MYTABLE

CodePudding user response：

it makes no sense, to have two date columns in the first place as they show the same content.

You format the date only when you select the data and you not keep it that way in the database.

The original data has stil teh possibiliy to use date/time functions and the first has not.

You can use DATE_FORMAT to get almoszt any format you want and need, so please use it and don't save redundant data in your database

CREATE tABLe mytable (mydate date)

INSERT INTO mytable VALUES('2019-01-28')

SELECT DATE_FORMAT(mydate,'%d/%m/%Y') FROM mytable

| DATE_FORMAT(mydate,'%d/%m/%Y') |
| :----------------------------- |
| 28/01/2019                     |

db<>fiddle here