SQL SERVER 中日期时间函数使用

SQL Server 日期时间函数使用

[TOC]

<h1>日期时间函数使用</h1>

## 1. 函数介绍

> 注意：在 SQL Server 里：0 = 字符串 '1900-01-01'这是 SQL Server 的基准日期（epoch date）

```SQL
SELECT CAST(0 AS DATETIME)
-- 结果：1900-01-01 00:00:00.000
----------------------------------------------
DATEDIFF(MONTH, 0, @startDate)
-- 等价于
DATEDIFF(MONTH, '1900-01-01', @startDate)
```

> 使用 0 而不直接使用 '1900-01-01 00:00:00.000' 的好处
+ 写法更短
+ 不依赖字符串格式，不会因为日期格式报错
+ SQL Server 通用写法，所有版本都支持
+ 性能更好（数字运算比字符串快）

> 注意：在 SQL Server 里：-1 = 字符串 '1899-12-31 00:00:00.000' 是 0 的前 1 天，同理 -2 就是 0 的前2天。反过来 1 就是 0 的后一天，2 是 0 的后 2 天

```SQL
SELECT CAST(-1 AS DATETIME)    -- 1899-12-31 00:00:00.000
SELECT CAST(-2 AS DATETIME)    -- 1899-12-30 00:00:00.000
SELECT CAST(1 AS DATETIME)     -- 1900-01-02 00:00:00.000
SELECT CAST(2 AS DATETIME)     -- 1900-01-03 00:00:00.000
```

### 1.1. DATEDIFF()函数

在 SQL SERVER 中，DATEDIFF 函数用于计算两个日期之间的差值，返回的是指定时间间隔的数量

#### 1.1.1. 语法

```SQL
DATEDIFF (datepart , startdate , enddate)
```

**各参数含义及使用说明如下：**

+ datepart（必选）：指定计算差值的时间单位，常见取值包括：
 + year（年）、quarter（季度）、month（月）
 + day（日）、week（周）、hour（时）
 + minute（分）、second（秒）、millisecond（毫秒）
+ startdate（必选）：表示起始日期的表达式，可以是日期常量、列名或函数返回值。
+ enddate（必选）：表示结束日期的表达式，与 startdate 类型需一致。

返回值：返回一个整数，表示 enddate 与 startdate 之间跨越的 datepart 单位数量。

#### 1.1.2. 示例

+ 计算日期间隔

```SQL
-- day 也可以使用 DD 来代替
SELECT DATEDIFF(day, '2023-01-01', '2023-01-10')
-- 输出结果：9
```

+ 计算跨月间隔

```SQL
-- 返回月份差（忽略天数）
SELECT DATEDIFF(month, '2023-01-31', '2023-02-01'); -- 结果：1
```

+ 计算年龄（以年为单位）

```SQL
-- 返回当前日期与出生日期之间的年数
SELECT DATEDIFF(year, '1990-05-15', GETDATE());
```

+ 结合其他函数使用

```SQL
-- 计算两个时间点之间的小时数
SELECT DATEDIFF(hour, '2023-01-01 08:00:00', '2023-01-01 14:30:00'); -- 结果：6
```

#### 1.1.3. 注意事项

|注意事项|描述|
|--|--|
|时间单位差异|使用week时，返回的是周数（每 7 天为一周）<hr>使用month或year时，仅比较日期的月 / 年部分，忽略具体天数|
|日期顺序|如果startdate晚于enddate，返回负值<hr>示例：DATEDIFF(day, '2023-01-10', '2023-01-01') 返回 -9|
|数据类型兼容性|startdate和enddate需为有效的日期 / 时间类型（如datetime、date等）|
|跨时区问题|若涉及不同时区的日期，建议先统一时区再计算差值|
|溢出精度问题|两个时间相差的太大，超出了换算的有效范围<hr/>示例：DATEDIFF(SECOND, '1970-01-01', '1900-01-01 00:00:00.000')，这里使用秒作为单位，两个时间按秒算超出了int的有效范围它就会报错：<mark>datediff 函数导致溢出。用于分隔两个日期/时间实例的日期部分的数字太大。请尝试使用日期部分精度较低的 datediff。</mark>(通常是 int 类型的最大值 2,147,483,647)，当出现这类提示时可以试试`DATEDIFF_BIG`进行计算<hr/><small>注意：1970-01-01 距今已超过 50 年，对应的秒数早已超过 int 类型的最大值（约 68 年），所以使用秒级计算必然会导致溢出</small>|

### 1.2. DATEADD()函数

## 2. 常见使用案例

### 2.1. 上个月最后一天

```SQL
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, '2022-02-28'), -1)
-- 输出结果：2022-01-31 00:00:00.000
-- DATEDIFF(MONTH, 0, @date) 就是计算目标日期距离 1900-01-01 的总月数，配合 DATEADD 就能快速取上个月第一天
```

### 2.2. 本月1号

```SQL
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, '2022-02-28'), 0)
-- 输出结果：2022-02-01 00:00:00.000
```

### 2.3. 当年第一天

```SQL
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
```

### 2.4. 当天 0 点

```SQL
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
```