对日期进行算术运算

在 SQL Server 中使用函数处理数据

Ana Voicu

Data Engineer

日期运算类型

使用算术运算符（+, -）
修改日期值 - DATEADD()
返回两个日期的差 - DATEDIFF()

算术运算

DECLARE @date1 datetime = '2019-01-01';
DECLARE @date2 datetime = '2020-01-01';

SELECT 
    @date2 + 1 AS add_one,
    @date2 - 1 AS subtract_one,
    @date2 + @date1 AS add_dates,
    @date2 - @date1 AS subtract_date;

| add_one                 | subtract_one            | add_dates               | subtract_date           |
|-------------------------|-------------------------|-------------------------|-------------------------|
| 2020-01-02 00:00:00.000 | 2019-12-31 00:00:00.000 | 2139-01-01 00:00:00.000 | 1901-01-01 00:00:00.000 |

DATEADD(datepart, number, date)

在日期上加减一定数量的时间单位

SELECT 
    first_name,
    birthdate,
    DATEADD(YEAR, 5, birthdate) AS fifth_birthday,
    DATEADD(YEAR, -5, birthdate) AS subtract_5years,
    DATEADD(DAY, 30, birthdate) AS add_30days,
    DATEADD(DAY, -30, birthdate) AS subtract_30days
FROM voters;

| first_name | birthdate  | fifth_birthday | subtract_5years | add_30days | subtract_30days |
|------------|------------|----------------|-----------------|------------|-----------------|
| Carol      | 1989-01-15 | 1994-01-15     | 1984-01-15      | 1989-02-14 | 1988-12-16      |
| Dennis     | 1972-03-11 | 1977-03-11     | 1967-03-11      | 1972-04-10 | 1972-02-10      |

DATEDIFF(datepart, startdate, enddate)

返回两日期之间的单位数

SELECT 
    first_name,
    birthdate,
    first_vote_date,
    DATEDIFF(YEAR, birthdate, first_vote_date) AS age_years,
    DATEDIFF(QUARTER, birthdate, first_vote_date) AS age_quarters,
    DATEDIFF(DAY, birthdate, first_vote_date) AS age_days,
    DATEDIFF(HOUR, birthdate, first_vote_date) AS age_hours
  FROM voters;

| first_name | birthdate  | first_vote_date | age_years | age_quarters | age_days | age_hours |
|------------|------------|-----------------|-----------|--------------|----------|-----------|
| Carol      | 1989-01-15 | 2015-03-09      | 26        | 104          | 9549     | 229176    |
| Dennis     | 1972-03-11 | 2013-10-29      | 41        | 167          | 15207    | 364968    |

一起练习吧！

在 SQL Server 中使用函数处理数据