MSSQL如何优雅地处理时间格式

1. SQL中时间格式的存储

在MSSQL中，日期和时间数据存储为数字，这些数字表示自 1900 年 1 月 1 日起的日期和时间的偏移量。日期类型包括 datetime、smalldatetime 和 date。

1.1 datetime

datetime 数据类型可存储日期和时间数据，从 1753 年 1 月 1 日到 9999 年 12 月 31 日，精度为3.33毫秒。

datetime 格式为: YYYY-MM-DD HH:MI:SS

-- 例如：将'2022-01-01 10:00:00'插入datetime类型的列中
CREATE TABLE MyTable
(
    id INT PRIMARY KEY,
    dateColumn DATETIME
)
INSERT INTO MyTable (id, dateColumn) VALUES(1, '2022-01-01 10:00:00')

1.2 smalldatetime

smalldatetime 数据类型也可存储日期和时间数据，从 1900 年 1 月 1 日到 2079 年 6 月 6 日，精度为 1 分钟。

smalldatetime 格式类似于 datetime：YYYY-MM-DD HH:MI:SS

-- 例如：将'2022-01-01 10:00:00'插入smalldatetime类型的列中
CREATE TABLE MyTable
(
    id INT PRIMARY KEY,
    dateColumn SMALLDATETIME
)
INSERT INTO MyTable (id, dateColumn) VALUES(1, '2022-01-01 10:00:00')

1.3 date

date 数据类型只能存储日期，从 0001 年 1 月 1 日到 9999 年 12 月 31 日。

date 格式为: YYYY-MM-DD

-- 例如：将'2022-01-01'插入date类型的列中
CREATE TABLE MyTable
(
    id INT PRIMARY KEY,
    dateColumn DATE
)
INSERT INTO MyTable (id, dateColumn) VALUES(1, '2022-01-01')

2. SQL中时间格式的转换

在SQL中，我们可以使用 CONVERT 函数或 CAST 函数将日期和时间数据类型转换为其他日期和时间数据类型或字符类型。

2.1 CONVERT 函数

CONVERT 函数可以将一个日期或时间类型的值转换为另一个日期或时间类型的值。

CONVERT 函数的语法如下：

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

其中，data_type 表示要转换为的数据类型；expression 表示要转换的表达式；style 表示转换样式。

以下是不同日期时间类型及样式的转换：

-- datetime to date
SELECT CONVERT(DATE, '2022-01-01 10:00:00', 120)
-- datetime to time
SELECT CONVERT(TIME, '2022-01-01 10:00:00', 120)
-- datetime to varchar
SELECT CONVERT(VARCHAR(20), '2022-01-01 10:00:00', 120)
-- varchar to datetime
SELECT CONVERT(DATETIME, '2022/01/01 10:00:00', 120)
-- varchar to date
SELECT CONVERT(DATE, '2022/01/01', 120)
-- varchar to time
SELECT CONVERT(TIME, '10:00:00', 108)

在转换样式（style）中，不同的样式代表不同的日期时间格式。

2.2 CAST 函数

CAST 函数可以将一个日期或时间类型的值转换为另一个日期或时间类型的值。

CAST 函数的语法如下：

CAST ( expression AS data_type [ ( length ) ] )

其中，expression 表示要转换的表达式；data_type 表示要转换为的数据类型；length 可选，表示转换的长度。

以下是不同日期时间类型及长度的转换：

-- datetime to date
SELECT CAST('2022-01-01 10:00:00' AS DATE)
-- datetime to time
SELECT CAST('2022-01-01 10:00:00' AS TIME)
-- datetime to varchar
SELECT CAST('2022-01-01 10:00:00' AS VARCHAR(20))
-- varchar to datetime
SELECT CAST('2022/01/01 10:00:00' AS DATETIME)
-- varchar to date
SELECT CAST('2022/01/01' AS DATE)
-- varchar to time
SELECT CAST('10:00:00' AS TIME)

3. SQL中时间格式的比较

在SQL中，我们可以使用比较运算符比较日期和时间数据类型之间的大小关系。

3.1 datetime 和 smalldatetime 比较

datetime 和 smalldatetime 均可以使用大于（>）、小于（<）、等于（=）等比较运算符。

DECLARE @dt1 DATETIME = '2022-01-01 10:00:00'
DECLARE @dt2 SMALLDATETIME = '2022-01-01 10:00:00'
IF @dt1 > @dt2
    PRINT '@dt1 is greater than @dt2'
ELSE IF @dt1 < @dt2
    PRINT '@dt1 is less than @dt2'
ELSE
    PRINT '@dt1 is equal to @dt2'

3.2 datetime 和 date 比较

datetime 和 date 比较也可以使用大于（>）、小于（<）、等于（=）等比较运算符。

DECLARE @dt DATETIME = '2022-01-01 10:00:00'
DECLARE @dt2 DATE = '2022-01-01'
IF @dt > @dt2
    PRINT '@dt is greater than @dt2'
ELSE IF @dt < @dt2
    PRINT '@dt is less than @dt2'
ELSE
    PRINT '@dt is equal to @dt2'

3.3 date 和 time 比较

date 和 time 也可以进行比较，同样使用大于（>）、小于（<）、等于（=）等比较运算符。

DECLARE @d DATE = '2022-01-01'
DECLARE @t TIME = '10:00:00'
IF @d > @t
    PRINT '@d is greater than @t'
ELSE IF @d < @t
    PRINT '@d is less than @t'
ELSE
    PRINT '@d is equal to @t'

4. SQL中时间格式的计算

在SQL中，我们可以对日期和时间进行加、减运算，得到一个新的日期或时间。

4.1 datetime 和 smalldatetime 计算

datetime 和 smalldatetime 都可以进行加、减运算。

DECLARE @dt DATETIME = '2022-01-01 10:00:00'
DECLARE @dt2 SMALLDATETIME = '2022-01-01 09:00:00'
-- 计算时间差
SELECT DATEDIFF(MINUTE, @dt, @dt2)
-- 时间加减
SELECT DATEADD(MINUTE, 30, @dt)
SELECT DATEADD(SECOND, -20, @dt)

4.2 date 和 time 计算

date 和 time 都可以进行加、减运算。

DECLARE @d DATE = '2022-01-01'
DECLARE @t TIME = '10:00:00'
-- 把时间加到日期上
SELECT DATEADD(HOUR, 2, CAST(@d AS DATETIME) + CAST(@t AS DATETIME))

5. 小结

MSSQL中日期和时间数据类型包括datetime、smalldatetime和date。我们可以使用CONVERT函数和CAST函数将日期和时间数据类型转换为其他日期和时间数据类型或字符类型。同时，我们还可以使用比较运算符比较日期和时间数据类型之间的大小关系，对日期和时间数据进行加、减运算。