SQL Server日期时间格式转换：CONVERT样式代码详解与性能优化

做SQL Server开发的人迟早会遇到这样的需求把一个datetime字段输出成2024-01-15这种纯日期或者拼成20240115作为流水号又或者要把2024年1月15日这样的中文格式塞进报表里。很多人第一反应是拿字符串函数去截取结果在转换上反复踩坑。其实SQL Server里早就内置了一个专门的函数CONVERT用于日期时间格式转换只要把样式代码记清楚格式化可以做得又快又稳。这篇内容我会围绕CONVERT的语法、样式代码、实操场景、性能问题、常见报错这几个方向展开尽量用真实能跑的例子说话。适合刚接触SQL Server的人也适合写过一段时间但每次都要现查样式代码的朋友。全文不会讲太多虚的都是平时写存储过程、写报表、做数据导出时能直接用上的东西。1. 初识CONVERT一个函数解决日期时间格式转换1.1 CONVERT能做什么解决什么问题CONVERT在SQL Server里本质上是一个类型转换函数作用就是把一个数据类型的表达式转换成另一种数据类型。比如把字符串换成数字、把数字换成字符串、把日期时间换成字符串都是它的活儿。但在实际开发中它被用得最多的场景之一就是日期时间类型和字符串之间的互转尤其是配上第三个参数style可以实现各种自定义的日期时间格式输出。举个例子你有一个订单表下单时间字段是datetime类型存的值是2024-01-15 14:30:22.123这种完整格式。但业务方在报表里只需要日期不想要时间那直接SELECT OrderDate输出给前端前端还得自己截取麻烦不说还容易出错。后端直接给个格式化好的字符串就省事多了SELECT CONVERT(VARCHAR(10), OrderDate, 120) AS OrderDateText FROM Orders;120就是我要重点介绍的一个样式代码它对应的是yyyy-mm-dd hh:mi:ss这种格式而VARCHAR(10)刚好截取了前10位得到的就是2024-01-15。CONVERT解决的核心问题可以总结成三件事第一控制日期时间在字符串输出时的形态第二让字符串能按指定格式安全地转回日期时间第三统一不同区域、不同语言环境下日期时间的表达方式避免你以为的格式和数据库认为的格式对不上。1.2 语法拆解三个参数分别怎么填CONVERT的基础语法是CONVERT ( data_type [ ( length ) ] , expression [ , style ] )三个参数其实都不难理解data_type目标数据类型做日期时间格式化时基本都是VARCHAR、NVARCHAR这类字符串类型偶尔也会用到DATE、DATETIME、SMALLDATETIME等日期类型。expression要被转换的原始值可以是一个日期时间类型的列、变量也可以是能隐式转换成日期时间的字符串。style可选的样式代码整数类型。它决定了日期时间转换成字符串时的格式或者字符串解析成日期时间时的解析规则。很多人会忽略第三参数但如果你做的是日期时间格式转换第三参数才是灵魂。不写style的情况下CONVERT(VARCHAR, GETDATE(), )会输出类似Jan 15 2024 2:30PM这种依赖语言环境的格式这种结果在中文环境下非常不友好输出到前端也容易闹笑话。所以我的习惯是只要涉及日期时间转字符串style必须显式写清楚。需要注意一个细节data_type如果指定了长度而格式化的结果超过了这个长度SQL Server会直接截断不会报错。比如CONVERT(VARCHAR(10), GETDATE(), 121)后面的毫秒部分就被截掉了得到2024-01-15 14:30。这个特性有时候是好事可以当截取工具用但有时候也会造成莫名其妙的数据丢失建议在定义长度时心里有数。2. 日期时间样式代码全解2.1 最常用的8个样式代码样式代码是CONVERT手里的格式字典。SQL Server官方文档里列了一长串从0到131很多新手一看就懵。但实际开发中用得到的就那么几个我先按使用频率把最常用的8个列出来样式代码输出格式示例基于2024-01-15 14:30:22.123适用场景120yyyy-mm-dd hh:mi:ss2024-01-15 14:30:22通用日志、系统间接口传参121yyyy-mm-dd hh:mi:ss.mmm2024-01-15 14:30:22.123需要保留毫秒的日志、ETL抽取23yyyy-mm-dd2024-01-15纯日期展示、按天分区112yyyymmdd20240115文件名命名、字典序排序111yyyy/mm/dd2024/01/15部分报表前端组件兼容格式108hh:mi:ss14:30:22纯时间展示101mm/dd/yyyy01/15/2024美式日期、国际系统数据交换126yyyy-mm-ddThh:mi:ss.mmm2024-01-15T14:30:22.123XML、JSON、ISO 8601标准格式其中120和121是我个人最推荐的通用格式因为它们不依赖任何语言环境不管服务器的LANGUAGE设成英文还是中文输出结果都一样稳定性极好。做系统对接、写日志、存ETL中间表用这两个基本不会踩坑。112虽然看起来不起眼但在做按天分区的文件名、流水号前缀时特别好用。比如生成一个20240115开头的订单导出文件名直接CONVERT(VARCHAR(8), GETDATE(), 112)就搞定了零额外处理。2.2 完整样式速查表除了常用8个剩下的样式代码也不是没用只是适用面窄一些。我把日常可能会碰到的都整理成表方便你直接查样式代码输出格式示例日期部分基于2024-01-150mon dd yyyy hh:miAM/PMJan 15 2024 2:30PM1mm/dd/yy01/15/242yy.mm.dd24.01.153dd/mm/yy15/01/244dd.mm.yy15.01.245dd-mm-yy15-01-246dd mon yy15 Jan 247mon dd, yyJan 15, 248hh:mi:ss14:30:2210mm-dd-yy01-15-2411yy/mm/dd24/01/1512yymmdd24011513dd mon yyyy hh:mi:ss:mmm15 Jan 2024 14:30:22:12314hh:mi:ss:mmm14:30:22:12320yyyy-mm-dd hh:mi:ss2024-01-15 14:30:2221yyyy-mm-dd hh:mi:ss.mmm2024-01-15 14:30:22.12322mm/dd/yy hh:mi:ss AM/PM01/15/24 2:30:22 PM24hh:mi:ss14:30:2225yyyy-mm-dd hh:mi:ss.mmm2024-01-15 14:30:22.123100mon dd yyyy hh:miAM/PMJan 15 2024 2:30PM102yyyy.mm.dd2024.01.15103dd/mm/yyyy15/01/2024104dd.mm.yyyy15.01.2024105dd-mm-yyyy15-01-2024106dd mon yyyy15 Jan 2024107mon dd, yyyyJan 15, 2024109mon dd yyyy hh:mi:ss:mmmAM/PMJan 15 2024 2:30:22:123PM110mm-dd-yyyy01-15-2024113dd mon yyyy hh:mi:ss:mmm15 Jan 2024 14:30:22:123114hh:mi:ss:mmm14:30:22:123126yyyy-mm-ddThh:mi:ss.mmm2024-01-15T14:30:22.123127yyyy-mm-ddThh:mi:ss.mmmZ2024-01-15T14:30:22.123Z130dd mon yyyy hh:mi:ss:mmmAM/PM15 Jan 2024 2:30:22:123PM回历日期131dd/mm/yy hh:mi:ss:mmmAM/PM15/01/24 2:30:22:123PM回历日期这里需要提醒一下1到7以及0这类没有yyyy完整年份的样式输出结果会受到服务器的语言设置影响。比如6号样式dd mon yy在英文环境下输出15 Jan 24在中文环境下可能输出15 1月 24。如果数据要跨系统交换这类依赖语言的样式尽量不要用否则解析方一个不留神就报错。2.3 选型思路不同场景该用哪个样式面对这么多样式代码很多人会问到底该背哪个我的建议是别硬背按照场景来选记住几条规则就够了。第一条规则跟外部系统对接、写日志、做ETL优先用120或121。这两个格式是纯数字加短横线和冒号任何系统、任何语言解析都不容易产生歧义。如果强调精度需要毫秒就用121不需要毫秒就用120还能少几个字符的存储量。第二条规则只是展示给用户看的日期用23最合适它就是标准ISO日期格式yyyy-mm-dd简洁、可读性强。国内报表系统基本都认这种格式。第三条规则需要参与排序、拼接字符串、生成文件名的场景用112。因为yyyymmdd是纯数字字典序就是时间先后顺序做字符串排序时不会出错。第四条规则涉及XML、JSON或者遵循ISO 8601标准的接口用126或127。126输出带毫秒的紧凑格式中间有个T分隔日期和时间非常典型。127比126多个末尾的Z表示UTC时间。做国际化项目时这两个格外有用。3. 实操从格式化到业务落地3.1 经典场景一把datetime变成纯日期字符串最常见的一个需求就是只要日期、不要时间。假设有一张打卡记录表Attendance里面有个字段CheckTime记录打卡时间现在要按天统计每个员工的打卡次数那分组条件就必须是日期部分。低效的写法是用各种字符串函数去截取SELECT CAST(YEAR(CheckTime) AS VARCHAR(4)) - RIGHT(0 CAST(MONTH(CheckTime) AS VARCHAR(2)), 2) - RIGHT(0 CAST(DAY(CheckTime) AS VARCHAR(2)), 2) AS CheckDate, COUNT(*) AS Cnt FROM Attendance GROUP BY CAST(YEAR(CheckTime) AS VARCHAR(4)) - RIGHT(0 CAST(MONTH(CheckTime) AS VARCHAR(2)), 2) - RIGHT(0 CAST(DAY(CheckTime) AS VARCHAR(2)), 2);这么写很啰嗦而且性能也不占优势。用CONVERT一行就搞定了SELECT CONVERT(VARCHAR(10), CheckTime, 23) AS CheckDate, COUNT(*) AS Cnt FROM Attendance GROUP BY CONVERT(VARCHAR(10), CheckTime, 23);两种写法结果一样但第二种清晰得多也更好维护。把23换成112就能得到20240115这种紧凑格式用于按天分区命名特别方便。这里我特别强调一下CONVERT(VARCHAR(10), CheckTime, 23)这种做法是先转字符串再截断它确实能拿到yyyy-mm-dd但前提是你截断的位置刚好是对的。如果你不小心把长度定义成VARCHAR(9)就会得到2024-01-1这种半截数据前端渲染直接崩。所以更稳妥的方式是用CAST(CheckTime AS DATE)先把datetime转成date类型再按需格式化。比如SELECT CONVERT(VARCHAR(10), CAST(CheckTime AS DATE), 23) AS CheckDate FROM Attendance;CAST(CheckTime AS DATE)是把数据真正降维成日期不留时间尾巴然后再用CONVERT控制输出格式逻辑上更干净。3.2 经典场景二报表文件名和流水号拼接第二个高频场景是生成带时间的文件名、流水号。比如每天导出一份订单明细文件文件名要带上日期时间防止重名。常见需求是order_20240115_143022.csv这种形式其中时间精确到秒。这时最方便的做法是直接用CONVERT拼两个部分SELECT order_ CONVERT(VARCHAR(8), GETDATE(), 112) _ CONVERT(VARCHAR(8), GETDATE(), 108) .csv AS FileName;112给的是20240115108给的是14:30:22拼出来就是order_20240115_143022.csv干净利落。如果要用带毫秒的可以用114得到14:30:22:123但文件名里带毫秒一般没必要而且冒号在Windows文件名里其实是不允许的反而找麻烦。顺带说一个坑用CONVERT拼字符串的时候要留意拼接结果的长度会不会超出目标变量的定义。比如你定义VARCHAR(30)但文件名前缀一长加上日期时间很容易超拼接结束时也不会报错只是数据被静默截断等到下游拿文件名时才发现不对。建议要么把变量长度放宽要么用LEFT函数再控制一层避免隐患。3.3 经典场景三范围和跨系统数据交换做数据查询时经常要处理查某一天的数据这种区间查询。很多人会写WHERE CONVERT(VARCHAR(10), OrderDate, 120) 2024-01-15这写法在功能上没错但性能上有问题具体原因我放到后面第4节讲。这里先给出更合理的写法WHERE OrderDate 2024-01-15 00:00:00 AND OrderDate 2024-01-16 00:00:00或者用CAST先定边界WHERE OrderDate CAST(2024-01-15 AS DATETIME) AND OrderDate DATEADD(DAY, 1, CAST(2024-01-15 AS DATETIME))关键点在于不要让查询条件对列本身套函数或转换而是把常量字符串先转成日期值再去比较。这样索引才能正常用上。跨系统数据交换方面CONVERT的样式码还能帮忙把外部传来的字符串安全地转成日期。比如某个上游接口传过来2024-01-15 14:30:22你想把它存进datetime字段直接CAST通常也能成功因为这种格式SQL Server认识。但如果上游传的是20240115这种紧凑格式直接CAST就会报错必须告诉SQL Server你给的到底是什么格式SELECT CONVERT(DATETIME, 20240115, 112);第三个参数112在这里的作用就是明确告诉SQL Server后面的字符串是yyyymmdd格式请按这个规则解析。这让字符串转日期变得非常可靠不必依赖服务器当前的DATEFORMAT设置。4. 性能、兼容性与跟CAST/FORMAT的对比4.1 为什么不要对查询列直接套CONVERT很多SQL优化文章都会提到一个概念叫SARGable也就是能利用索引 的能力。简单说如果一个查询条件能被SQL Server优化成索引查找Index Seek它就是SARGable如果不行只能做索引扫描Index Scan性能就差远了。当你在WHERE子句中对列本身套了CONVERT比如WHERE CONVERT(VARCHAR(10), OrderDate, 120) 2024-01-15SQL Server必须先对每一行的OrderDate做一次格式化然后再跟右边的字符串比较这个操作破坏了索引的有序性导致查询优化器无法直接走索引查找。数据量小的时候感觉不明显一旦表里几百万行这种写法的查询可能慢好几倍。最典型的错误是把CONVERT当万能钥匙动不动就对列套一层。正确的姿势是把条件右边的常量转换好或者直接给日期区间让列本身保持原始类型参与比较。比如按月汇总某个月的数据低效写法是WHERE CONVERT(VARCHAR(7), OrderDate, 120) 2024-01高效的写法是WHERE OrderDate 2024-01-01 AND OrderDate 2024-02-01这段含义完全等价但执行计划天差地别。我做过一个实际案例某张流水表3000多万行原先用CONVERT(VARCHAR(10), CreateTime, 120) day查一天的数据需要跑8秒改成区间查询后只需几十毫秒性能差距接近百倍。这种优化成本极低回报却极大。4.2 CAST、CONVERT、FORMAT怎么选SQL Server里做日期时间转换有三兄弟CAST、CONVERT、FORMAT。我经常被问它们到底有什么区别什么时候用哪个。CAST是ANSI SQL标准语法写法简单SELECT CAST(GETDATE() AS DATE) AS Today;它只能做类型转换不能指定格式。想拿到yyyy-mm-dd可以把datetime转成date再输出但如果你想拿到2024-01-15这种固定格式的字符串CAST就无能为力了。CONVERT是SQL Server特有的扩展比CAST多了第三个style参数可以精确控制日期时间格式。这是两者最大的区别。如果你需要控制格式选CONVERT如果只是单纯换类型CAST更简洁而且可移植性更好。FORMAT是SQL Server 2012开始引入的函数它借助.NET的格式字符串来格式化写起来最灵活SELECT FORMAT(GETDATE(), yyyy-MM-dd) AS Today; SELECT FORMAT(GETDATE(), yyyy年MM月dd日) AS TodayCn;FORMAT能做的事情比CONVERT多得多尤其是中文化格式、自定义分隔符几乎为所欲为。但它有个致命弱点性能非常差。因为FORMAT底层要调用.NET的CLR开销远高于CONVERT。在我测过的例子里对100万行数据做FORMAT比CONVERT慢10倍以上。所以我的选型原则很简单能拿CONVERT解决的格式绝不碰FORMAT。只有在CONVERT确实做不出你要的格式时比如中文年月日、自定义千分位才在数据量小、非频繁调用的场景下用FORMAT。报表查询结果集通常也就几千行用FORMAT无所谓但在批量ETL、循环处理里用了FORMAT等于自己给自己挖坑。4.3 不同SQL Server版本和语言环境下的表现差异CONVERT的日期时间样式码从SQL Server 2000时期就基本成型了所以SQL Server 2008、2012、2016、2019、2022这些版本里常见的样式码表现是一致的。这意味着你手上这套写法拿到老库、新库上基本都能跑兼容性非常强。但有几个细节需要留意。第一FORMAT函数是SQL Server 2012才引入的如果你公司还在用SQL Server 2008 R2那代码里就不能用FORMAT必须硬着头皮用CONVERT或者其他字符串处理方式。第二TRY_CONVERT函数也是SQL Server 2012引入的用于安全转换转换失败时返回NULL而不是报错。这个函数结合CONVERT在数据清洗中非常好用但同样老版本不支持。语言环境方面服务器的LANGUAGE设置会影响以英文月份缩写为输出的样式代码比如0、6、7、100、106、107等。做国际项目时最好只用纯数字样式码101-114系列、120、121、126、127这样不管部署在哪个国家的服务器上输出都一样不会出现这里跑得好好的搬到国外服务器就变成英文月份的烦恼。日期格式的解析也一样。SQL Server对字符串转日期的解析受SET DATEFORMAT影响。比如字符串15/01/2024在dmy格式下是2024年1月15日在mdy格式下就可能解析失败或者变成别的日期。用CONVERT并显式指定样式码就能绕开这个不确定性SELECT CONVERT(DATETIME, 15/01/2024, 103);103表示dd/mm/yyyy这样写不管会话的DATEFORMAT是什么SQL Server都会按日/月/年的顺序解析不会闹出1月15日变成15月1日这种乌龙。5. 常见报错与排查实录5.1 Conversion failed when converting date and/or time from character string这是SQL Server里最经典的日期转换报错几乎每个开发都遇到过。完整信息大概是Conversion failed when converting date and/or time from character string.意思是SQL Server拿到一个字符串想把它转成日期时间类型但是失败了。原因通常有两种第一种是字符串本身不是合法日期比如2024-13-45月份13、日期45根本不存在第二种是字符串格式无法被当前语言环境正确识别比如你给的是15/01/2024但SQL Server当前按mdy解析它会把15当月份自然就失败了。排查思路我一般分三步。第一步用ISDATE函数快速判断一下字符串到底是不是合法日期SELECT ISDATE(2024-01-15); -- 返回1 SELECT ISDATE(2024-13-45); -- 返回0第二步如果字符串本身合法但还是报错那大概率是格式解析问题这时用CONVERT加显式样式码告诉SQL Server按什么格式解析。第三步实在不确定字符串里混了什么脏数据可以用TRY_CONVERT代替CONVERT转换失败时返回NULL方便你进一步排查SELECT TRY_CONVERT(DATETIME, 2024-01-15, 120) AS ValidDate; SELECT TRY_CONVERT(DATETIME, unknown, 120) AS InvalidDate; -- 返回NULL生产环境做数据清洗时我强烈建议先用TRY_CONVERT把坏的日期数据筛出来看看具体是哪些格式再针对性地处理而不是让整个存储过程因为一条脏数据直接崩掉。5.2 字符串转日期时被本地化格式坑到这类问题最容易出现在看起来没问题的代码里。比如你有一个参数DateStr前端传过来12/05/2024你直接CAST(DateStr AS DATETIME)有时候成功有时候失败。为什么因为SQL Server解析这个字符串时受SET DATEFORMAT和默认语言影响在中文环境下可能按ymd或ymd解析成功在英文环境下可能按mdy解析成功结果同一段代码在不同环境里跑出不同结果。我遇到过最典型的一次同一套存储过程在测试库中文环境里跑得好好的部署到生产库英文环境后某条日期解析突然报错。排查了一下午发现就是CONVERT没写样式码导致SQL Server按不同语言规则解析mm/dd/yyyy格式的字符串英文环境下把13当成了月份自然就炸了。解决方式不复杂所有从外部传入的日期字符串一律用CONVERT加显式样式码CONVERT(DATETIME, DateStr, 101) -- 明确mm/dd/yyyy CONVERT(DATETIME, DateStr, 103) -- 明确dd/mm/yyyy CONVERT(DATETIME, DateStr, 120) -- 明确yyyy-mm-dd hh:mi:ss这样写不管服务器语言是什么解析规则都固定下来才不会出现开发环境正常、生产环境报错的尴尬。5.3 日期时间存储与前端显示的偏移问题很多人会忽略datetime和datetime2的精度差异导致CONVERT格式化的结果跟预期不一致。datetime类型的精度是3.33毫秒也就是说它只能精确到毫秒的1/300存储时会做四舍五入。当你往datetime字段里插入2024-01-15 14:30:22.1234实际存下来的可能是14:30:22.123并不是你插入的原始值。datetime2则能精确到100纳秒精度高得多。所以在做格式转换时如果你的源字段是datetime用CONVERT输出毫秒时结果可能跟业务系统里的原始时间有细微差别。比如业务系统显示的是14:30:22.1234数据库里存的却是14:30:22.123你用121格式化出来就是...22.123看起来像数据对不上。实际上不是CONVERT的锅是字段精度本身决定的。如果你对时间精度有严格要求在建表时尽量用datetime2而不是datetime。SQL Server 2008以上版本都支持datetime2新项目直接默认datetime2即可。已经用了datetime的老表如果业务上必须保留毫秒级别更精确的值就要考虑改字段类型否则格式化输出永远只能拿到约等于的值。5.4 排查清单速查表我把日常最容易碰到的日期时间格式转换问题整理成一个速查表遇到问题可以对着查症状可能原因排查/解决办法字符串转日期时报Conversion failed字符串非法或格式不被识别用ISDATE判断、用TRY_CONVERT定位脏数据、显式指定样式码同样代码不同环境结果不同依赖了SET DATEFORMAT或语言环境字符串解析时都带上style参数格式化结果比预期少几位VARCHAR长度不够被截断检查data_type长度或改用CAST转date再格式化毫秒值跟源系统不一致字段是datetime精度不足改用datetime2类型查询特别慢索引用不上WHERE里对列用了CONVERT改成范围查询保持列原始类型参与比较输出中文月份或英文月份不稳定使用了依赖语言的样式码换成纯数字格式样式如120、121、112这个表不是标准答案但覆盖了绝大多数我实际处理过的案例。你要是遇到奇葩问题核心思路还是那三条先确认字符串本身合不合法再确认格式解析规则是否明确最后确认是不是精度或长度限制导致的隐性截断。最后再分享一个小技巧CONVERT的样式码虽然多但真正需要背下来的就三个23纯日期、120日期时间、112紧凑日期。这三个能覆盖日常80%的需求。剩下的遇到再查完全不影响干活。你如果经常要跟JSON、XML接口打交道那就多记一个126它输出的是标准ISO 8601格式很多外部系统都认这个。我个人平时还有个习惯就是在写存储过程或者创建视图时把所有日期时间输出统一用120或121规范掉不在SQL层做奇怪的字符串拼接也不依赖前端去解析。这样数据库返回的数据格式始终稳定前端拿到直接展示省去大量联调和扯皮。日期时间格式转换看起来是个小功能但用好了整个数据链路的稳定性都会上一个台阶。