I want to creat a computed column based on datediff function between 2 existing columns (date1 and date2). (in days)
我想基于2个现有列（date1和date2）之间的datediff函数创建一个计算列。 （以天为单位）
date1 and date2 are sql DATE type.
date1和date2是sql DATE类型。
What I tried without sucess :
我没有成功的尝试：
ALTER TABLE my_table ADD lenght AS datediff('dd', date1, date2)
Thank you for helping.
感谢您的帮助。
2 个解决方案
#1
1
A GENERATED column is automatically updated when the values it refers to in other columns change. The correct syntax is:
当GENERATED列在其他列中引用的值发生更改时，它会自动更新。正确的语法是：
ALTER TABLE my_table ADD length INT GENERATED ALWAYS AS (DATEDIFF('day', date1, date2))
#2
2
ALTER TABLE my_table ADD lenght AS int;
UPDATE my_table SET lenght = DateDiff('dd', date1, date2);
-- Don't forget to add a trigger that fires on updated and inserted rows that will keep the value of lenght valid if the date1 or date2 changes
#1
1
A GENERATED column is automatically updated when the values it refers to in other columns change. The correct syntax is:
当GENERATED列在其他列中引用的值发生更改时，它会自动更新。正确的语法是：
ALTER TABLE my_table ADD length INT GENERATED ALWAYS AS (DATEDIFF('day', date1, date2))
#2
2
ALTER TABLE my_table ADD lenght AS int;
UPDATE my_table SET lenght = DateDiff('dd', date1, date2);
-- Don't forget to add a trigger that fires on updated and inserted rows that will keep the value of lenght valid if the date1 or date2 changes