CODEWITHSUNDEEP
mysqlsqlmariadbcalculated-columnsdate-arithmetic
dysonsphere
dysonsphere

Reputation: 33

How can I add a generated column in MySQL/MariaDB that will caculate a date from information in other columns?

I understand how to add a generated column using MariaDB (supports the same syntax as MySQL) and have done so with this table using a simple arithmetic operation. However when I try to use the DATE_SUB function I get the syntax error:

SQL Error [1064] [42000]: (conn=28594) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WEEKS)) STORED' at line 3

The code I am using is:

ALTER TABLE `ExampleTable`
ADD COLUMN `DOB` DATE
AS (DATE_SUB (`DateExpected`, INTERVAL `Age` WEEKS)) STORED;

I would like to calculate DOB from DateExpected and Age values.

From the CREATE TABLE:

  `DateExpected` date DEFAULT NULL,
  `Age` int(11) DEFAULT NULL,

Substituting with:

AS (DATE_SUB('1998-01-02', INTERVAL 31 DAY) STORED;

gives a similar error:

SQL Error [1064] [42000]: (conn=28594) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'STORED' at line 3

But substituting with:

AS (`DateExpected`) STORED;

works just fine at populating the DOB column with values from DateExpected, so I suppose this has to do with the DATE_SUB statement.

Upvotes: 1

Views: 1543

Answers (1)

GMB
GMB

Reputation: 222462

You can do:

ALTER TABLE `ExampleTable`
    ADD COLUMN `DOB` DATE
    AS (`DateExpected` - INTERVAL `Age` WEEK) STORED;

Upvotes: 1

Related Questions