热线电话:13121318867

登录
首页大数据时代SQL Server 日期转换利器:CONVERT 函数完全指南
SQL Server 日期转换利器:CONVERT 函数完全指南
2026-11-17
收藏

在日常数据库开发与报表查询中,日期格式几乎是最高频的"打交道"对象:页面上要展示 yyyy-MM-dd,接口要返回 ISO 8601 字符串,导入数据时又要把 dd/MM/yyyy 解析回日期类型。SQL Server 提供了专门的 CONVERT 函数,通过一个 style 编号就能在日期类型与字符类型之间按指定格式互转。本文梳理其语法、常用格式对照表、典型场景示例,以及容易踩坑的细节。

一、CONVERT 函数语法

与纯类型转换的 CAST 不同,CONVERT 是 SQL Server 特有的转换函数,多出一个 style 参数来控制日期的呈现格式:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
  • data_type:目标数据类型,如 VARCHAR、NVARCHAR、DATE、DATETIME2。
  • expression:待转换的表达式,通常是日期列、GETDATE() 等日期函数或日期字符串。
  • style:整数编号,指定日期时间的输入/输出格式;传 NULL 时结果也为 NULL。

一个最基础的例子——把当前时间转成字符型:

SELECT CONVERT(NVARCHAR(30), GETDATE(), 120) AS CurrentTime;
-- 输出:2026-11-17 11:16:00

二、style 编号对照表

SQL Server 的样式编号分两类:不带世纪数位(年份为 yy,两位)和带世纪数位(年份为 yyyy,四位)。生产环境建议优先使用四位年份的编号,避免世纪歧义。

1. 常用日期时间格式

两位年份 四位年份 标准/地区 输出格式
1 101 美国 mm/dd/yy / mm/dd/yyyy
2 102 ANSI yy.mm.dd / yyyy.mm.dd
3 103 英国/法国 dd/mm/yy / dd/mm/yyyy
4 104 德语 dd.mm.yy / dd.mm.yyyy
5 105 意大利语 dd-mm-yy / dd-mm-yyyy
6 106 — dd mon yy / dd mon yyyy
7 107 — mon dd, yy / mon dd, yyyy
8 / 24 108 — hh:mi:ss
9 109 默认 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM(或 PM)
10 110 美国 mm-dd-yy / mm-dd-yyyy
11 111 日本 yy/mm/dd / yyyy/mm/dd
12 112 ISO yymmdd / yyyymmdd
13 113 欧洲(24 小时制) dd mon yyyy hh:mi:ss:mmm
14 114 — hh:mi:ss:mmm(24 小时制)
20 120 ODBC 规范 yyyy-mm-dd hh:mi:ss(24 小时制)
21 / 25 121 ODBC 规范(毫秒) yyyy-mm-dd hh:mi:ss.mmm(24 小时制)
22 — 美国 mm/dd/yy hh:mi:ss AM(或 PM)
— 23 — yyyy-mm-dd(纯日期)
— 126 ISO 8601 yyyy-mm-ddThh:mi:ss.mmm
— 127 ISO 8601 时区 yyyy-mm-ddThh:mi:ss.fffZ

表中 mon 表示月份缩写,其具体英文/中文写法受会话语言(SET LANGUAGE)影响;mmm 为毫秒,当毫秒值为 0 时,这一段小数部分不会显示。

”

2. 几个高频编号速记

  • 120 / 20:yyyy-MM-dd HH:mm:ss,国内报表最常用的"标准时间"格式。
  • 23:yyyy-MM-dd,只取日期部分。
  • 112:yyyyMMdd,紧凑无分隔符,常用于分区、文件命名。
  • 126:ISO 8601 带 T 分隔,适合接口、JSON、XML 传参。
  • 103:dd/MM/yyyy,英式写法,注意与美式 101(MM/dd/yyyy)区分。

三、典型场景示例

1. 日期转字符串:按指定格式展示

DECLARE @dt DATETIME = '2026-11-17 13:39:17.450';

SELECT
  CONVERT(VARCHAR(10), @dt, 23)  AS DateOnly,      -- 2026-11-17
  CONVERT(VARCHAR(19), @dt, 120) AS DateTimeStd,  -- 2026-11-17 13:39:17
  CONVERT(VARCHAR(8),  @dt, 112) AS CompactDate,  -- 20261117
  CONVERT(VARCHAR(30), @dt, 126) AS Iso8601;      -- 2026-11-17T13:39:17.450

2. 字符串转日期:按已知格式解析入库

反向转换时,style 同样用于告诉 SQL Server 字符串遵循哪种格式:

SELECT
  CONVERT(DATETIME, '17/11/2026', 103) AS FromBritish,  -- dd/MM/yyyy
  CONVERT(DATETIME, '2026.11.17', 102) AS FromAnsi,     -- yyyy.mm.dd
  CONVERT(DATETIME, '20261117',   112) AS FromCompact;  -- yyyymmdd

3. 只保留时间部分

SELECT CONVERT(VARCHAR(8), GETDATE(), 108) AS TimeOnly;  -- 13:39:17

4. 输出 ISO 8601 格式(含毫秒与时区标记)

SELECT CONVERT(NVARCHAR(30), GETDATE(), 126) AS IsoTime;
-- 2026-11-17T13:39:17.450

5. 在 WHERE 条件中按日期过滤

先把日期列统一截到当天,避免时分秒影响范围匹配:

SELECT OrderId, OrderTime
FROM dbo.Orders
WHERE CONVERT(DATE, OrderTime) = CONVERT(DATE, GETDATE());

四、CONVERT 与 CAST 的区别

CAST 只能做类型之间的强制转换,无法指定格式;CONVERT 多了 style 这一格式控制入口。两者返回结果在不显式指定样式时基本等价:

SELECT
  CAST(GETDATE() AS NVARCHAR(30))      AS UsingCast,   -- 走默认样式
  CONVERT(NVARCHAR(30), GETDATE(), 0)  AS UsingConvert;

简单结论:只换类型用 CAST,要控制日期/数值呈现格式用 CONVERT。

五、容易踩坑的细节

1. 月份缩写受会话语言影响

样式 6、7、9、106、107、109 等含 mon 的格式,月份会按当前语言渲染。同一日期在英文会话下是 Nov 17 2026,切换语言后写法可能变化。若输出要跨环境稳定,优先使用数字月份的样式(如 120、23、112)。

2. 两位数年份的世纪歧义

不带世纪数位的样式(编号小于 100)依赖"两位数年份截止"配置,默认截止年份为 2049:49 被解释为 2049,50 被解释为 1950。建议始终使用四位年份样式(101 及以上),从源头避免歧义。

3. 字符长度不足会截断

目标 VARCHAR/NVARCHAR 长度不够时,结果会被直接截断,且不会报错。例如格式串长达 19 位却只声明了 VARCHAR(10),就会丢尾部。建议按格式预留长度:日期用 10,日期时间用 19~30。

4. 部分字符串转日期属于"不确定性转换"

编号低于 100 的样式,以及 106、107、109、113、130 等,在字符串与日期互转时属于不确定性转换,可能影响索引使用、计划缓存与结果稳定性(样式 20、21 除外)。跨系统传日期,推荐使用无歧义的 120、121、126。

5. datetime2 / datetimeoffset 的默认样式

在兼容级别 110 及以上,time、datetime2、datetimeoffset 默认样式为 121;早期版本计算列默认样式为 0。升级数据库后,若历史结果依赖旧格式,需显式指定 style 复核。另外,日期时间类型转换到 datetimeoffset 时,只支持样式 0 或 1,其余会报 9809 错误。

6. 日期范围别越界

DATETIME 的最小年份为 1753 年,而 DATE、DATETIME2 最小可到 0001 年。把更早的日期转成 DATETIME 会触发 242 号"超出范围"错误。

六、实践建议

  1. 对外接口、跨系统传输:统一用 126(ISO 8601)或 121,格式无歧义、可被各类语言标准解析。
  2. 国内后台报表:日期用 23,日期时间用 120,可读性最好。
  3. 紧凑编码、分区键、文件名:用 112(yyyyMMdd),无分隔符、排序天然有序。
  4. 解析外部字符串:先确认源系统格式,再选对应样式;能拿到四位年份就不要用两位年份样式。
  5. 避免在索引列上做格式转换:把转换放在常量侧,或用范围条件(>= 当天 且 < 次日),以保留索引效率。

掌握 CONVERT 与这套 style 编号,日期格式化、解析与过滤的绝大多数场景都能直接覆盖;配合上面的避坑清单,可以减少大量"日期对不上、格式不统一"的线上问题。

推荐学习书籍 《CDA一级教材》适合CDA一级考生备考,也适合业务及数据分析岗位的从业者提升自我。完整电子版已上线CDA网校,累计已有10万+在读~ !

免费加入阅读:https://edu.cda.cn/goods/show/3151?targetId=5147&preview=0

数据分析师资讯
更多

OK
客服在线
立即咨询
客服在线
立即咨询