Adding a calculated date column to a MySQL dataset

Question

I have a column dateTime which consists of dates of the format "MM-DD-YYYY, hh-mm-ss" and I need to create a STORED column on the same table to get rid of the time element. I've tried:

ALTER TABLE table ADD COLUMN startOfDay AS(date(dateTime)) STORED;

but this gives a wrong syntax error. How do I make it work? I think the error is due to the AS part.

Answer 1

First when asking a question and you tell that you have a error, always show the error message in your post.

Secondly to use STORED columns you need MySQL 5.7 instance or higher. At the moment I only have a 5.6 instance running so I can't test the query. But looking at the MySQL documentation I would suggest the following query syntax:

ALTER TABLE <table-name> ADD COLUMN <column-name> DATE GENERATED ALWAYS AS (DATE_FORMAT(<name-of-datetime-column>, `%Y-%m-%d`)) STORED COMMENT '<description>';

Just replace the placeholders with the names you have. To be sure and learn how things work, always check the MySQL reference manual on the subject.

See: https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html