日期和时间

最新推荐文章于 2025-06-04 09:16:36 发布

Annx

最新推荐文章于 2025-06-04 09:16:36 发布

阅读量2.9k

点赞数

CC 4.0 BY-SA版权

文章标签：日期和时间函数日期和时间类型

本文链接：https://blog.youkuaiyun.com/AnnDCorner/article/details/81198948

SQL Server 2012 T-SQL 基础教程--读书专栏收录该内容

7 篇文章

订阅专栏

本文介绍了SQL Server中日期和时间的处理，包括日期范围筛选、日期时间函数如CURRENT_TIMESTAMP、CAST、CONVERT、PARSE等的使用，以及DATEADD、DATEDIFF等函数的应用。同时，讲解了如何高效使用索引避免全表扫描，和如何处理时区问题。还提到了查询元数据的相关工具，如目录视图和信息架构视图。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

日期范围筛选

筛选日期范围时很自然的会想到 YEAR、MONTH 函数。例如筛选2017年2月创建的公司：

SELECT Name, CreateTime FROM Companies WHERE YEAR(CreateTime)=2017 AND MONTH(CreateTime)=2

但是，在筛选列上应用操作时，SQL Server 不能以有效方式使用索引，而进行全表扫描。我们应尽量避免在筛选列上执行操作，可以通过修改谓词，从而高效使用索引。

SELECT Name, CreateTime FROM Companies WHERE CreateTime>='20170201' AND CreateTime<'20180301'

日期和时间函数

当前日期和时间

SELECT
   [GetDate]=GETDATE(),                     -- DATETIME       当前日期和时间
   [CurrentTimestamp]=CURRENT_TIMESTAMP,    -- DATETIME       等同于GETDATE，但是遵从 ANSI SQL
   [GetUTCDate]=GETUTCDATE(),               -- DATETIME       当前UTC日期和时间（通用协调时间）
   [SysDatetime]=SYSDATETIME(),             -- DATETIME2      当前日期和时间
   [SysUTCDatetime]=SYSUTCDATETIME(),       -- DATETIME2      当前UTC日期和时间（通用协调时间）
   [SysDatetimeOffset]=SYSDATETIMEOFFSET()  -- DATETIMEOFFSET 包含时区偏移量的当前日期时间

CURRENT_TIMESTAMP 和 GETDATE 返回相同的结果，但是前者是标准函数，建议使用前者。SQL Server 没有仅返回当前系统日期或系统时间的函数，不过可以通过 CAST 把 CURRENT_TIMESTAMP 或 SYSDATETIME 转换到 DATE 或 TIME了类型：

SELECT
    CurrentDate=CAST(SYSDATETIME() AS DATE),
    CurrentTime=CAST(SYSDATETIME() AS TIME)

CAST、CONVERT 和 PARSE 函数，以及 TRY_对应函数
CAST、CONVERT 和 PARSE 函数用于将输入值转换为目标类型，如果转换成功，则函数返回转换后的值，否则会导致查询失败。3个函数分别都有对应的称为 TRY_CAST、TRY_CONVERT、TRY_PARSE 的函数。每个前缀为 TRY_的函数接受与其对应函数相同的输入，执行相同的操作，但前缀为 TRY_的函数接受的输入如果不能转换为目标类型，函数会返回 NULL，而不是查询故障。
语法：
     CAST(value AS datatype)
     TRY_CAST(value AS datatype)
     CONVERT(datatype,value[,style_number])
     TRY_CONVERT(datatype,value[,style_number])
     PARSE(value AS datetype [USING culture])
     TRY_PARSE(value AS datetype [USING culture])
3个基本函数转输入的 value 到指定的目标 datatype。CONVERT 的第三个可选参数 style_number 可以指定转换的样式，可以在 SQL Server 2017文档的CAST and CONVERT(Transact-SQL)部分找到完整的样式编号列表以及它们的含义。同样，PARSE 函数也支持一个区域性说明参数，参见SQL Server 2017文档PARSE (Transact-SQL)。
当转换字符串为日期或时间数据类型时，某些字符串的格式是与语言相关的。推荐使用与语言无关的格式，或在使用 CONVERT/PARSE 函数时显示地指定样式编号或区域编号，这样无论运行的登录语言是什么，代码能够以相同的方式解释。CAST 是 ANSI，而 CONVERT 和 PARSE 不是，所以除非需要使用样式编号或区域编号，不然建议使用 CAST 函数。
```
SELECT CAST('20180725' AS DATE)                                    -- 2018-07-25
SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112)                    -- 20180725
SELECT CAST(CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) AS DATETIME)  -- 2018-07-25 00:00:00.000
SELECT CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114)                   -- 17:46:20:627
SELECT CAST(CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114) AS DATETIME) -- 1900-01-01 17:47:49.987

-- 使用美式英语区域
SELECT PARSE('01/12/2018' AS DATETIME USING 'en-US')               -- 2018-01-12 00:00:00.000

-- 使用英式英语区域
SELECT PARSE('01/12/2018' AS DATETIME USING 'en-GB')               -- 2018-12-01 00:00:00.000
```

SWITCHOFFSET 函数
SWITCHOFFSET 函数将输入的 DATETIMEOFFSET 值调整为指定的时区。
语法
SWITCHOFFSET(datetimeoffset_value,time_zone)

-- 将当前 datetimeoffset 值调整为 +08:00 时区（北京时间）
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+08:00')  -- +08:00时区

-- 将当前 datetimeoffset 值调整为 -01:00 时区
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-01:00')  -- -01:00时区

-- 将当前 datetimeoffset 值调整为 UTC（协调通用时间）
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00')  -- +00:00时区

TODATETIMEOFFSET 函数
TODATETIMEOFFSET 函数设置输入的日期和时间的时区偏移量。
语法
TODATETIMEOFFSET(date_and_time_value,time_zone)
TODATETIMEOFFSET 函数与 SWITCHOFFSET 不同的是，第一个参数通常是一个不知道偏移量的日期和时间类型值，它简单地将时区偏移量与输入日期和时间合并成一个新的 datetimeoffset 值。通常用于迁移非已知偏移量数据到已知偏移量数据。
```
SELECT TODATETIMEOFFSET(GETDATE(), '+05:00')  -- 2018-07-26 09:39:44.737 +05:00
SELECT TODATETIMEOFFSET(GETDATE(), '+08:00')  -- 2018-07-26 09:39:44.737 +08:00
```
DATEADD 函数
DATEADD 函数允许为指定的日期部分增加一个指定的单位数量到输入的日期和时间值中。
语法
DATEADD(datepart,number,date)
datepart 的有效输入值包括 year、quarter、month、dayofyear、day、week、weekday、hour、minute、second、millisecond、microsecond 和 nanosecond，也可以使用缩写形式指定 datepart，参见 SQL Server 2017文档DATEADD (Transact-SQL)。
```
SELECT DATEADD(YY, 1, '20180726') -- 2019-07-26 00:00:00.000
SELECT DATEADD(M, 1, GETDATE())  -- 2018-08-26 09:59:06.757
```

DATEDIFF 函数
DATEDIFF 函数返回两个日期和时间之间在指定日期部分的差异。
语法
DATEDIFF(datepart,date_val1,date_val2)

-- 返回val2 比 val1 多的天数，可以为负数 30
SELECT DATEDIFF(DD, '20180627', '20180727')

-- 当前系统午夜日期
SELECT DATEADD(DD, DATEDIFF(DD, '20180101', CURRENT_TIMESTAMP), '20180101')

-- 当月第一天的午夜时间
SELECT DATEADD(M, DATEDIFF(M, '20180101', CURRENT_TIMESTAMP), '20180101')

-- 当年第一天的午夜时间
SELECT DATEADD(YY, DATEDIFF(YY, '20180101', CURRENT_TIMESTAMP), '20180101')

-- 0 默认为 1900-01-01 00:00.000
SELECT DATEADD(YY, DATEDIFF(YY, 0, CURRENT_TIMESTAMP), 0)

DATEPART 函数
DATEPART 函数返回一个表示所请求日期和时间值部分的整数。
语法
DATEPART(datepart, val)
datepart 参数的有效值包括year、quarter、month、dayofyear、day、week、weekday、hour、minute、second、millisecond、microsecond、nanosecond、TZoffset 和 ISO_WEEK。
```
SELECT DATEPART(DD, '20180727')  -- 27
```
YEAR、MONTH 和 DAY 函数
YEAR、MONTH 和 DAY 函数是 DATEPART 函数的缩写形式，返回一个表示输入日期和时间的年、月和日部分的整数。
语法
YEAR(val),MONTH(val),DAY(val)
```
SELECT YEAR('20180727'), MONTH('20180727'), DAY('20180727')-- 2018  7   27
```

DATENAME 函数
DATENAME 函数返回一个代表日期和时间值部分的字符串。
语法
DATENAME(datepart,val)
DATENAME 函数具有和 DATEPART相同的输入参数，但是它返回的是所请求部分的名称，而不是数字。

SET LANGUAGE 简体中文;
SELECT DATENAME(M, '20180727')  -- 07

SET LANGUAGE us_english;
SELECT DATENAME(M, '20180727')  -- July

SET LANGUAGE Italian;
SELECT DATENAME(M, '20180727')  -- luglio

查询当前语言，和查询所有的语言：

select @@language;
SELECT langid, name FROM sys.syslanguages ORDER BY langid

ISDATE 函数
ISDATE 函数接收一个字符串输入，如果能转换为日期和时间数据类型则返回1，否则返回0。
语法
ISDATE(string)
```
SELECT ISDATE('20180701'), ISDATE('2018070')  -- 1  0
```

FROMPARTS 函数
FROMPARTS 函数接受代表日期和时间值各个部分的整数，并根据这些部分构建一个所请求类型的值。
语法
     DATEFROMPARTS(year, month, day)
     DATETIME2FROMPARTS(year, month, day, house, minute, seconds, fractions, precision)
     DATETIMEFROMPARTS(year, month, day, house, minute, seconds, fractions)
     DATETIMEOFFSETFROMPARTS(year, month, day, house, minute, seconds, fractions, house_offset, minute_offset, precision)
     SMALLDATETIMEFROMPARTS(year, month, day, house, minute)
     TIMEFROMPARTS(house, minute, seconds, fractions, precision)

SELECT
    DATEFROMPARTS(2018, 07, 27),                                       -- 2018-07-27
    DATETIME2FROMPARTS(2018, 07, 27, 17, 32, 30, 997, 3),              -- 2018-07-27 17:32:30.997
    DATETIMEFROMPARTS(2018, 07, 27, 17, 32, 30, 997),                  -- 2018-07-27 17:32:30.997
    DATETIMEOFFSETFROMPARTS(2018, 07, 27, 17, 32, 30, 997, +8, 0, 3),  -- 2018-07-27 17:32:30.997 +08:00
    SMALLDATETIMEFROMPARTS(2018, 07, 27, 17, 32),                      -- 2018-07-27 17:32:00
    TIMEFROMPARTS(17, 32, 30, 997, 3)                                  -- 17:32:30.997

EOMONTH 函数
EOMONTH 函数接收一个日期和时间值，返回相应月末凌晨日期作为 DATE 数据类型。第二个可选参数表示要增加的月数。
语法
EOMONTH(input[, months_to_add])
```
SELECT EOMONTH(CURRENT_TIMESTAMP)  -- 2018-07-31
```

查询元数据

SQL Server 提供获取对象元数据信息（目录视图、信息架构视图、系统存储过程和函数）的工具，参见Transact-SQL 引用（数据库引擎）。

目录视图

-- 查询数据库中表及其架构名称 SCHEMA_NAME 函数将架构 ID 转换为架构名字
SELECT table_schema_name=SCHEMA_NAME(SCHEMA_ID), table_name=name FROM sys.tables

-- 查询表中列的信息 TYPE_NAME 函数将系统类型 ID 转换为名称
SELECT
    column_name=name,                       -- 列名称
    column_type=TYPE_NAME(system_type_id),  -- 数据类型
    max_length,                             -- 最大长度
    collation_name,                         -- 排序规则名称
    is_nullable                             -- 为空性
FROM sys.columns
WHERE object_id=OBJECT_ID(N'dbo.Companies')

信息架构视图

信息架构视图是一个视图集合，位于名为 INFORMATION_SCHEMA 的架构中，并以标准方式提供元数据信息。

-- 查询当前数据库中的用户表以及它们的架构名称
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=N'BASE TABLE'

-- 列名、数据类型、最大长度、排序规则名称、为空性
SELECT
    COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA=N'dbo' AND TABLE_NAME=N'Companies'

系统存储过程和函数

-- 当前数据库中能够查询到的对象列表
EXEC sys.sp_tables;

-- 返回对象常用信息的多个结果集，以及经列、索引、约束等信息
EXEC sys.sp_help @objname=N'dbo.Companies'

-- 返回对象中列的信息
EXEC sys.sp_columns @table_name=N'Companies', @table_owner='dbo'

-- 返回对象中的约束信息
EXEC sys.sp_helpconstraint @objname=N'dbo.Companies'

-- 返回当前实例的所请求属性信息，ProductLevel：产品级别
SELECT SERVERPROPERTY('ProductLevel')  -- SP1

-- 返回指定数据库名称的所请求属性信息，Collation：排序规则
SELECT DATABASEPROPERTYEX(N'TSQL2012', 'Collation')  -- Chinese_PRC_CI_AS

-- 返回指定对象的所请求属性信息，TableHasPrimaryKey：表 Companies 是否具体主键
SELECT OBJECTPROPERTY(OBJECT_ID(N'dbo.Companies'), 'TableHasPrimaryKey')  -- 1

-- 返回指定列的所请求属性信息，Companies 表的 Company 列是否可以为空
SELECT COLUMNPROPERTY(OBJECT_ID(N'dbo.Companies'), N'Company', 'AllowsNull')  -- 0