英文:
SQL - How do I populate the age column if only the year of birth was given?
问题
数据只有一个出生年份的列，标记为year_birth。
我添加了一个年龄列，现在我想通过将当前年份减去year_birth列中的值来给这个列添加数值，以显示年龄。
"year_birth" "age"
1970
1961
1958
1967
1989
1958
1954
1967
1954
1954
这个查询返回了一个语法错误
-- 更新年龄列
update marketing_data
set age = DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),year_birth)), '%Y')
+ 0
错误: 函数datediff(timestamp with time zone, integer)不存在
第4行: set age = DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),year_birth)),...
^
提示: 没有与给定名称和参数类型匹配的函数。您可能需要添加显式类型转换。
SQL状态: 42883
字符: 77
英文:
The data only has the year of birth column, labeled year_birth.
I added an age column but now I would like to add values to this column to show the age by subtracting the current year by the values in the year_birth column.
"year_birth" "age"
1970
1961
1958
1967
1989
1958
1954
1967
1954
1954
this query returned a syntax error
-- Update Age column
update marketing_data
set age = DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),year_birth)), '%Y')
+ 0
ERROR: function datediff(timestamp with time zone, integer) does not exist
LINE 4: set age = DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),year_birth)),...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 77
答案1
得分: 3
将你的year_birth转换为整数（int），如果它还不是整数。使用extract(year...)。
更新 my_data 表
设置 age = extract(year from now()) - year_birth::int;
英文:
Cast your year_birth as int if not already int. Use extract(year....
update my_data
set age = extract(year from now()) - year_birth::int;
答案2
得分: 0
根据错误信息，我们可以理解您并非使用MS SQL Server，而是使用PostgreSQL。
PostgreSQL没有名为datediff的函数。
您可以通过以下方式从year_birth列获取年龄：
UPDATE marketing_data
SET age = EXTRACT(YEAR FROM age(DATE(year_birth::text || '-01-01')));
这将根据year_birth的值计算出的年龄更新age列。
英文:
By error we can understand you are not using MS SQL-Server but rather PostgreSQL.
PostgreSQL does not have function named datediff.
You can get the age from year_birth column like below,
UPDATE marketing_data
SET age = EXTRACT(YEAR FROM age(DATE(year_birth::text || '-01-01')));
This will update the age column with age calculated from year_birth values.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论