SQL语句怎么算时间差

Question

将工作满25周年的员工薪水上调50%，工作满30周年的员工薪水上调80%，已知入职时间，怎么算出来几周年？SQL语句

Answer 1

假设你的入职时间存储在数据库中的字段名为"start_date"，你可以使用下面的 SQL 语句来算出工作周年：

SELECT 
    start_date,
    DATEDIFF(week, start_date, GETDATE()) as weeks_of_service
FROM 
    employees

接下来，你可以根据员工的周数差来计算薪水调整：

SELECT 
    start_date,
    DATEDIFF(week, start_date, GETDATE()) as weeks_of_service,
    CASE 
        WHEN DATEDIFF(week, start_date, GETDATE()) >= 25 AND DATEDIFF(week, start_date, GETDATE()) < 30 THEN salary * 1.5
        WHEN DATEDIFF(week, start_date, GETDATE()) >= 30 THEN salary * 2
        ELSE salary
    END AS adjusted_salary
FROM 
    employees