如何计算两日期间的年份？求创建num_years_worked列的SQL语句

不同SQL数据库的日期处理函数略有差异，以下是主流数据库的实现方式，按需选择：

MySQL/MariaDB #

使用TIMESTAMPDIFF函数直接计算两个日期间的完整年份差（会考虑月份和日期，避免仅按年份相减的误差）：

-- 查看计算结果
SELECT 
    start_date,
    end_date,
    TIMESTAMPDIFF(YEAR, start_date, end_date) AS num_years_worked
FROM your_table_name;

-- 永久添加列并赋值
ALTER TABLE your_table_name
ADD COLUMN num_years_worked INT;

UPDATE your_table_name
SET num_years_worked = TIMESTAMPDIFF(YEAR, start_date, end_date);

示例中2000/12/12到2010/10/12会返回9，因为结束日期的月份早于开始日期，未达到完整10年。

PostgreSQL #

通过AGE函数生成日期间隔，再提取年份部分：

-- 查看计算结果
SELECT 
    start_date,
    end_date,
    EXTRACT(YEAR FROM AGE(end_date, start_date)) AS num_years_worked
FROM your_table_name;

-- 永久添加列并赋值
ALTER TABLE your_table_name
ADD COLUMN num_years_worked INTEGER;

UPDATE your_table_name
SET num_years_worked = EXTRACT(YEAR FROM AGE(end_date, start_date))::INTEGER;

SQL Server #

DATEDIFF(YEAR)仅按年份边界计算，需额外判断月日修正结果：

-- 查看计算结果
SELECT 
    start_date,
    end_date,
    DATEDIFF(YEAR, start_date, end_date) 
    - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, start_date, end_date), start_date) > end_date 
           THEN 1 
           ELSE 0 
      END AS num_years_worked
FROM your_table_name;

-- 永久添加列并赋值
ALTER TABLE your_table_name
ADD num_years_worked INT;

UPDATE your_table_name
SET num_years_worked = DATEDIFF(YEAR, start_date, end_date) 
                       - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, start_date, end_date), start_date) > end_date 
                              THEN 1 
                              ELSE 0 
                         END;

通用注意事项 #

• 替换your_table_name为你的实际表名
• 如果start_date和end_date是字符串类型，需先转换为日期格式：
  • MySQL：STR_TO_DATE(start_date, '%Y/%m/%d')
  • PostgreSQL：TO_DATE(start_date, 'YYYY/MM/DD')
  • SQL Server：CONVERT(DATE, start_date, 111)

内容的提问来源于stack exchange，提问作者Whyte_Napkin