1. DATEADD 的参数账本三个参数各自在做什么上周有位做报表的同学在群里发了一句话DATEADD(month, 1, 2024-01-31)为什么返回的是 2 月 29 日而不是 3 月 3 日下面有人回因为 2 月没有 31 号。这个答案不算错但只说对了一半。真正需要搞清楚的是 DATEADD 这个函数在整个日期处理体系里扮演什么角色它和其他日期函数怎么分工以及它的三个参数各自会把结果往哪个方向带。先给结论DATEADD 是一个平移函数不是构造函数。它不会帮你补零、不会帮你格式化、也不会帮你判断月末它只做一件事——把给定的日期值沿着某条时间轴往前或往后推固定的步数。理解了这一点后面所有的用法都是它的推论。它的签名很干净DATEADD (datepart, number, date)三个参数位置固定少一个都不行。很多人卡住不是因为记不住名字而是因为不知道这三个位置分别吃什么类型、吐什么类型、边界在哪里。下面拆开说。1.1 第一个参数 datepart为什么它能写那么多种别名datepart是你告诉函数我要按哪个时间单位移动的地方。它接收的是一个关键字不是一个数值。SQL Server 允许的取值比大多数人想象的要多而且同一个单位往往有好几个等价写法这是历史兼容留下的包袱记住常用的一两个就够了但看到别人写的别名不能眼生。时间单位可选写法单次移动的实际跨度年year/yy/yyyy按日历年推进受闰年影响季度quarter/qq/q3 个月月month/mm/m1 个自然月年中的天dayofyear/dy/y1 天日day/dd/d1 天周week/wk/ww7 天工作日weekday/dw/w1 天小时hour/hh1 小时分钟minute/mi/n1 分钟秒second/ss/s1 秒毫秒millisecond/ms1 毫秒微秒microsecond/mcs1 微秒纳秒nanosecond/ns100 纳秒这张表里有两个坑值得单独拎出来说。第一个坑是m和mi。m是月份mi是分钟。这两个只差一个字母含义差了四万多倍。我在代码评审里见过不止一次有人想加一分钟写成DATEADD(m, 1, ...)跑出来日期直接跳到下个月因为数据量小、现象不明显上线之后才被业务方发现统计口径错了。第二个坑是week和weekday。很多人以为weekday是按工作日算的、会自动跳过周末实际上不是。在 SQL Server 里week是加 7 天weekday是加 1 天两者都只是纯天数平移完全不管这天是不是休息日。如果你想做跳过周末的五个工作日之后得自己写循环或者用日历表去关联别指望weekday帮你办这事。datepart传字符串也可以比如DATEADD(month, 1, ...)会被隐式转换。但我不建议这么写一是没必要多一层转换二是这种写法在部分工具链里会触发语法高亮错误白白浪费排查时间。1.2 第二个参数 number小数、负数与表达式的结果number决定移动多少格。它接收整数有三种使用形态经常被忽略。负数就是往回推。DATEADD(day, -7, ...)拿到的是一周前的同一时刻不需要换DATEDIFF再减也不用DATE_SUB这种别的数据库才有的函数。查近七天的数据时我基本都这么写边界可读性比一堆加减号好很多。小数会被截断不是四舍五入。这是最容易被误解的一处。实测一下SELECT DATEADD(day, 1.9, 2024-01-01) AS a, -- 2024-01-02 DATEADD(day, 1.1, 2024-01-01) AS b, -- 2024-01-02 DATEADD(day, -0.9, 2024-01-01) AS c; -- 2024-01-01三次结果都是往零的方向取整之后再平移。所以如果你在代码里做过总数 / 天数这种除法又把结果直接喂给number小数部分会静默丢失不会报错也不会提醒。想保留精度就得先把单位换成更细的粒度比如把1.5 小时改成DATEADD(minute, 90, ...)。表达式和变量可以直接传。number位置完全可以放一个计算式比如DATEADD(day, n * 2, base)或者放进CASE WHEN里做条件平移。这一点在做根据用户等级给不同的有效期这类逻辑时特别顺手不用在应用层拼字符串。1.3 第三个参数 date返回类型的连锁反应date参数看着最无害其实它决定了返回值的类型进而影响后面所有环节——包括能不能塞进目标列、比较时会不会出隐式转换、显示出来带不带时分秒。规则是这样的DATEADD 的返回类型跟随第三个参数的输入类型。传进去是date出来就是date传进去是datetime2(3)出来还是datetime2(3)精度也保持一致传进去是smalldatetime出来就是smalldatetime而且会被它的精度约束截断到分钟。最容易出事的是字符串字面量。你写DATEADD(day, 1, 2024-01-01)这个2024-01-01并不会被当成date而是按datetime解析的于是结果变成一个带00:00:00.000的datetime。把它插进date类型的列当然没问题但如果你拿它和另一个date列做比较、或者塞进一个varchar列结果就会带上那个多余的零点时间报表上显示出来就不好看。我的习惯是永远显式转换第三个参数写清楚我要的类型DATEADD(day, 1, CAST(2024-01-01 AS date))多敲几个字符省掉后面一堆格式排查。这个习惯我保持了几年确实少踩了不少返工。2. 按时间粒度逐层加一份可直接对照的实操清单把参数讲清楚之后剩下的就是哪种场景该用哪个粒度。这一段我按从大到小的顺序过一遍每个粒度配一个真实的用法场景你可以直接抄去改字段名。2.1 年、季、月跨度最大的三种加法按年推最常见的是算周年日、到期日SELECT DATEADD(year, 1, CAST(2024-03-15 AS date)); -- 2025-03-15闰日是个特殊情况2024-02-29往后推一年会落到2025-02-28函数会自动收敛到该月最后一天不会报错也不会给你一个 2 月 30 日。这个行为在算会员一年有效期时是需要跟业务确认的到底是给到 2 月 28 日还是 3 月 1 日两种口径都有人用函数默认给的是前者。按季度推等价于按月推 3 个月DATEADD(quarter, 1, ...)和DATEADD(month, 3, ...)结果完全一样。既然等价为什么还要留quarter这个关键字主要是为了代码可读性写下个季度的时候用quarter一眼就懂用month, 3还得在脑子里换算一下。团队协作里这点可读性是有价值的。按月推是使用频率最高的一个也是最容易出坑的一个具体见后面第 4 章。这里先给一个典型用法算账单日。-- 假设账期是从每月 15 号开始算下一个账期起始日 SELECT DATEADD(month, 1, CAST(2024-01-15 AS date)); -- 2024-02-152.2 周、日、工作日那个容易被混淆的 wk 与 dw按天推是最直白的SELECT DATEADD(day, 30, CAST(2024-01-01 AS date)); -- 2024-01-31 SELECT DATEADD(day, -1, CAST(2024-03-01 AS date)); -- 2024-02-29注意第二个例子往回推一天自动跨月跨到了闰日函数处理得很好不用你判断上个月有多少天。这也正是我推荐用它而不是自己拼字符串的原因——手写2024-02- (天数-1)这种代码闰年、大小月、跨年全得自己判断出错的概率太高。按周推就是按天推 7 天SELECT DATEADD(week, 2, CAST(2024-01-01 AS date)); -- 2024-01-15它和DATEADD(day, 14, ...)完全等价。有人担心week会受数据库的一周第一天设置影响这个担心放在DATEDIFF上是对的放在DATEADD上就不成立——DATEADD的week是纯粹的 7 天平移不查任何配置。weekday则是 1 天平移和day结果一致。存在这个别名的历史原因我猜是语法完整性实际写代码时基本不会有人用它看到了知道是加一天就行。2.3 时、分、秒、毫秒高频采样场景下的加法往下走到小时以下主要出现在两类场景一类是日志、监控、埋点数据的时间窗口对齐另一类是定时任务的触发时间计算。监控数据经常需要以 5 分钟为粒度聚合这时候可以用DATEADD把任意时间戳对齐到最近的整 5 分钟DECLARE t datetime2 2024-05-20 10:37:42; SELECT DATEADD(minute, DATEDIFF(minute, 0, t) / 5 * 5, 0) AS bucket; -- 2024-05-20 10:35:00这段写法的思路值得说一下DATEDIFF(minute, 0, t)算出从基准点0也就是 1900-01-01到目标时间一共过了多少分钟得到一个整数整数除以 5 再做整除把零头砍掉乘回 5 得到对齐后的分钟数最后用DATEADD把这个分钟数加回基准点还原成一个时间。整个过程没有任何字符串操作全是整数运算效率高而且不会因为格式问题出错。秒和毫秒的用法同理只是粒度更细SELECT DATEADD(second, -30, SYSUTCDATETIME()); -- 30 秒前 SELECT DATEADD(millisecond, 500, t); -- 加半秒微秒和纳秒需要datetime2类型才撑得住精度用datetime会被截断回约 3.33 毫秒的精度这一点在写高精度计时逻辑时必须注意否则你会觉得明明加了 100 微秒怎么没变化。3. 和 DATEDIFF 搭配把 DATEADD 用成日期截断器单独用DATEADD只是平移把它和DATEDIFF组合起来就变成了日期截断和区间构造的利器。这个组合技我认为是日期处理里性价比最高的一个套路掌握之后很多原本要写一堆CASE WHEN的需求都能一行搞定。3.1 构造本月一日零时这类基准点需求里出现频率极高的一类表达是本月一号本周一今天零点。这些都可以用同一个模板套出来DECLARE now datetime2 2024-05-20 14:37:42; -- 今天零点 SELECT DATEADD(day, DATEDIFF(day, 0, now), 0); -- 本月一号零点 SELECT DATEADD(month, DATEDIFF(month, 0, now), 0); -- 今年一月一号零点 SELECT DATEADD(year, DATEDIFF(year, 0, now), 0); -- 本周一零点需配合 SET DATEFIRST 1 SET DATEFIRST 1; SELECT DATEADD(week, DATEDIFF(week, 0, now), 0);模板的骨架是固定的DATEADD(单位, DATEDIFF(单位, 0, 目标时间), 0)。先算出目标时间距基准点差多少个完整单位再把这些单位原样加回基准点多出来的零头自然就被丢掉了。这个写法比CONVERT(varchar(7), now, 120) -01之类的字符串拼法好在哪一是快全是整数运算不用做类型转换二是稳不受语言设置、日期格式设置影响三是能直接参与比较和索引因为结果是标准的日期类型而不是字符串。写这套模板时有个细节要注意基准点0在 SQL Server 里代表1900-01-01。这个值本身没有特殊含义它只是一个干净的、各项都为 0 的起点用它来做差值计算最方便。但如果你的数据里有早于 1900 年的日期DATEDIFF会返回负数这套模板就不适用了需要换成更早的基准比如-0001-01-01。实际业务里极少遇到知道有这么个前提就行。3.2 用半开区间替代 BETWEEN 月末统计月度数据的时候很多人会这么写-- 不推荐 WHERE create_time BETWEEN 2024-05-01 AND 2024-05-31这里藏着两个问题。第一2024-05-31被当成datetime时会补上00:00:00导致 5 月 31 日当天 0 点之后的数据全部漏掉。第二如果create_time是datetime2或者带毫秒边界上还会再丢一截。正确姿势是用左闭右开的半开区间DECLARE start date 2024-05-01; SELECT ... FROM t WHERE create_time start AND create_time DATEADD(month, 1, start);DATEADD(month, 1, start)自动算出下个月一号不用你去查这个月是 28 天、30 天还是 31 天也不用担心闰年。区间左闭右开任何精度的字段都不会漏数据也不会重复统计。这个模式我建议直接固化成团队的编码规范。它的好处不只是正确性还在于扩展性如果要改成按季度统计把month换成quarter改成按年换成year改成按周换成week。整个查询结构一点不用动。3.3 生成连续日期序列的写法做报表时经常需要补齐没有数据的日期比如最近 30 天每天的订单量某几天没有订单也得显示 0。这时候得先造一个连续日期序列出来DATEADD正好可以当生成器用。WITH nums AS ( SELECT TOP (30) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n FROM sys.all_objects ) SELECT DATEADD(day, -n, CAST(GETDATE() AS date)) AS d FROM nums ORDER BY d;思路是先借一张系统表造出 0 到 29 的序号再用DATEADD(day, -n, 今天)逐个往前推得到 30 个日期。TOP (30)控制天数改数字就行。如果环境不允许访问系统表可以用递归 CTE但要注意默认递归上限是 100超过就得加OPTION (MAXRECURSION n)。在我做过的项目里日期序列一般不超过几百行递归完全够用但如果是按分钟生成一整年的序列五十多万行还是老老实实用数字表或者临时表更稳妥。4. 实测中最容易翻车的几处溢出、月末与类型隐式转换函数本身的语法很简单出问题的地方基本都集中在边界条件和类型转换上。这一章把我在实际项目里踩过的、以及帮别人排查过的几类问题整理出来。4.1 月末加法不是简单地加一个月这是DATEADD(month, ...)最反直觉的地方。看下面这组实测SELECT DATEADD(month, 1, CAST(2024-01-31 AS date)); -- 2024-02-29 SELECT DATEADD(month, 1, CAST(2024-02-29 AS date)); -- 2024-03-29 SELECT DATEADD(month, 1, CAST(2024-03-31 AS date)); -- 2024-04-30 SELECT DATEADD(month, -1, CAST(2024-03-31 AS date)); -- 2024-02-29四条结果放在一起看规律就清楚了目标月份没有那么长的天数时结果收敛到目标月的最后一天但一旦收敛过你再往后加起点就变成了收敛后的日期不会自动弹回原来的月末。第二行最能说明问题2024-02-29加一个月得到2024-03-29而不是2024-03-31。也就是说这种加法不具备往返一致性——先加一个月再减一个月不保证回到原点SELECT DATEADD(month, -1, DATEADD(month, 1, CAST(2024-01-31 AS date))); -- 2024-01-29不是 2024-01-31这个特性在算分期、算周期性账单的时候会积累误差。比如每月 31 号扣款到了 2 月变成 29 号3 月又变成 29 号之后一路都是 29 号永远回不到 31 号。如果业务上要求始终是月末正确做法是用锚点日期加序号-- 以每月 1 号为锚点先加月份再减一天得到目标月的最后一天 SELECT DATEADD(day, -1, DATEADD(month, n 1, CAST(2024-01-01 AS date)));这样每一次计算都从固定的2024-01-01出发误差不会累积。这个技巧我在做账期系统时用过确实比直接加月份靠谱。4.2 边界溢出的报错长什么样date、datetime、smalldatetime各自的取值范围不一样加超了会直接抛错不是返回 null也不是悄悄截断SELECT DATEADD(year, 1, CAST(9999-12-31 AS date)); -- 报错将值添加到 date 列导致溢出。datetime的下界更靠前往回推太远也会炸SELECT DATEADD(year, -1, CAST(1753-01-01 AS datetime)); -- 报错将值添加到 datetime 列导致溢出。这类错误在生产环境里通常出现在两种地方一是用户输入了极端的日期值而前端没做校验二是循环里用累加的方式推日期结果越推越远。第二种情况尤其隐蔽因为循环跑到第 9000 次才炸前面都好好的。防御办法很简单在关键路径上加一层范围判断CASE WHEN base 1901-01-01 THEN NULL ELSE DATEADD(year, 100, base) END或者在应用层就把输入限制在一个合理区间内。我倾向于两者都做数据库层兜底应用层给用户友好提示。4.3 把函数套在索引列上性能是怎么掉的这条是我见过代价最大的一个误用。写法大概长这样-- 索引失效 WHERE DATEADD(day, 1, create_time) 2024-01-01 -- 索引用得上 WHERE create_time DATEADD(day, -1, 2024-01-01)create_time上有索引的情况下第一种写法会让索引彻底用不上因为优化器没法为加了 1 天之后的值去索引里定位只能全表扫描逐行算。数据量几十万的时候看不出来上千万之后差别就是秒级和分钟级的区别。判断标准很朴素函数套在列上索引就废函数套在常量上索引照用。所以做日期平移的时候能挪到等号右边就挪过去。上面第二个写法把-1挪到了常量侧等价变换之后索引完好。如果实在没法挪比如两边都是列那就得考虑持久化计算列ALTER TABLE t ADD next_day AS DATEADD(day, 1, create_time) PERSISTED; CREATE INDEX IX_next_day ON t (next_day);DATEADD是确定性函数可以做持久化计算列并建索引。这个方案在需要频繁按偏移后的日期过滤时很有效代价是写入时多维护一个索引以及表结构变得更复杂。用不用取决于读写比例写多读少的表我不建议加。5. 换个数据库怎么写DATEADD 在不同方言里的对应物DATEADD这个名字严格来说不是 SQL 标准它更像 SQL Server 家族的方言。同一个需求搬到别的数据库上写法差异不小做迁移的时候这一块特别容易漏改。下面按常见的几种库做个对照。5.1 MySQL 的 DATE_ADD 与 INTERVAL 语法MySQL 没有DATEADD对应的是DATE_ADD(date, INTERVAL expr unit)SELECT DATE_ADD(2024-01-31, INTERVAL 1 MONTH); -- 2024-02-29 SELECT DATE_ADD(2024-01-01, INTERVAL -7 DAY); -- 2023-12-25 SELECT DATE_SUB(2024-01-01, INTERVAL 7 DAY); -- 同上翻写的时候有几个差异必须记住单位的位置不一样。SQL Server 是DATEADD(month, 1, d)单位在前MySQL 是DATE_ADD(d, INTERVAL 1 MONTH)单位和数值一起放在后面。写过一次就不会忘但批量迁移脚本里如果靠正则替换很容易搞错。单位名字大小写不敏感但单复数有区别。DAY和DAYS都行MONTH和MONTHS都行习惯上单数更常见。月末行为基本一致2024-01-31加一个月同样返回2024-02-29不会报错。这一点两边是对齐的迁移时不用额外处理。DATE_SUB是DATE_ADD加负数的一个语法糖语义上完全等价选哪个看团队习惯。5.2 PostgreSQL 的 interval 运算PostgreSQL 走的是另一条路——把时间间隔当成一等公民用interval字面量做加减SELECT DATE 2024-01-31 INTERVAL 1 month; -- 2024-02-29 SELECT DATE 2024-01-01 - INTERVAL 7 days; -- 2023-12-25 SELECT TIMESTAMP 2024-01-01 10:00 INTERVAL 90 minutes;也可以把数值和单位分开写方便把变量塞进去SELECT DATE 2024-01-01 (n || days)::interval FROM t; -- 或者更规范一点 SELECT DATE 2024-01-01 make_interval(days n) FROM t;这里有个容易踩的点PostgreSQL 的interval 1 month和interval 30 days不是一回事。前者按日历月推进后者是固定 30 天跨月的时候结果会不一样。写月度统计的时候必须用1 month用30 days会在 2 月出偏差。这个坑我在做跨库口径对比时踩过两边查同样的条件结果差了一天查了半天才发现是间隔单位的问题。5.3 Oracle 与 SQLite 的替代写法Oracle 没有专门的日期加减函数它是用算术运算符直接实现的-- 加 1 天 SELECT DATE 2024-01-01 1 FROM dual; -- 加 1 个月用专门的 ADD_MONTHS SELECT ADD_MONTHS(DATE 2024-01-31, 1) FROM dual; -- 2024-02-29 -- 加 1 小时1/24 天 SELECT DATE 2024-01-01 1/24 FROM dual; n的单位是天所以加小时、加分钟都得换算成分数。这种写法看着简洁但1/24、1/1440这类魔数散在代码里读起来很吃力我通常在项目里包一层自定义函数把语义显式化。ADD_MONTHS的月末行为和DATEADD(month, ...)一致也是收敛到最后一天。但要注意 Oracle 的DATE类型自带时分秒做日期比较时同样要用半开区间逻辑和前面讲的一样。SQLite 更简单它只有文本形式的日期全靠字符串函数SELECT date(2024-01-31, 1 month); -- 2024-02-29 SELECT datetime(2024-01-01 10:00, -30 minutes); SELECT date(now, 7 days);修饰符的写法是符号 数值 单位单位用复数英文单词。SQLite 没有真正的日期类型比较和排序都依赖 ISO 格式字符串的字典序所以格式必须是YYYY-MM-DD或者YYYY-MM-DD HH:MM:SS写成别的格式就全乱了。把这几种方言放在一张表里对照迁移时对照着改效率最高需求SQL ServerMySQLPostgreSQLOracleSQLite加 1 天DATEADD(day,1,d)DATE_ADD(d,INTERVAL 1 DAY)d INTERVAL 1 dayd 1date(d,1 day)加 1 个月DATEADD(month,1,d)DATE_ADD(d,INTERVAL 1 MONTH)d INTERVAL 1 monthADD_MONTHS(d,1)date(d,1 month)减 30 分钟DATEADD(minute,-30,d)DATE_ADD(d,INTERVAL -30 MINUTE)d - INTERVAL 30 mind - 30/1440datetime(d,-30 minutes)今天零点DATEADD(day,DATEDIFF(day,0,d),0)CURDATE()d::dateTRUNC(d)date(now)最后一行值得单独注意SQL Server 里取今天零点需要那个DATEDIFF组合拳而其他几家基本都有一行搞定的写法。这算是 SQL Server 在日期处理上一个不太方便的地方但用熟了模板之后也就是肌肉记忆的事。我在实际做跨库项目时的体会是别指望一套 SQL 走遍所有数据库。日期这块尤其如此各家在月末怎么算interval 是不是日历月有没有原生日期类型这几个点上的取舍完全不同硬要统一往往会在边界上出问题。比较务实的做法是在数据访问层做一层薄封装把日期加减抽象成方法底层按目标库分别实现业务代码只调方法不直接写函数名。这样即使后面换库改动量也控制在很小的范围里。另外再分享一个小技巧写日期相关的 SQL 时先在测试库里把月末、闰日、跨年这几个边界值都跑一遍把这些用例固化成单元测试比事后排查生产数据要省心得多。 
长河君 · 2026/10/31 18:38:32 · 阅读 1.2k SQL Server DATEADD日期加减：参数、月末边界与跨库写法
1. DATEADD 的参数账本三个参数各自在做什么上周有位做报表的同学在群里发了一句话DATEADD(month, 1, 2024-01-31)为什么返回的是 2 月 29 日而不是 3 月 3 日下面有人回因为 2 月没有 31 号。这个答案不算错但只说对了一半。真正需要搞清楚的是 DATEADD 这个函数在整个日期处理体系里扮演什么角色它和其他日期函数怎么分工以及它的三个参数各自会把结果往哪个方向带。先给结论DATEADD 是一个平移函数不是构造函数。它不会帮你补零、不会帮你格式化、也不会帮你判断月末它只做一件事——把给定的日期值沿着某条时间轴往前或往后推固定的步数。理解了这一点后面所有的用法都是它的推论。它的签名很干净DATEADD (datepart, number, date)三个参数位置固定少一个都不行。很多人卡住不是因为记不住名字而是因为不知道这三个位置分别吃什么类型、吐什么类型、边界在哪里。下面拆开说。1.1 第一个参数 datepart为什么它能写那么多种别名datepart是你告诉函数我要按哪个时间单位移动的地方。它接收的是一个关键字不是一个数值。SQL Server 允许的取值比大多数人想象的要多而且同一个单位往往有好几个等价写法这是历史兼容留下的包袱记住常用的一两个就够了但看到别人写的别名不能眼生。时间单位可选写法单次移动的实际跨度年year/yy/yyyy按日历年推进受闰年影响季度quarter/qq/q3 个月月month/mm/m1 个自然月年中的天dayofyear/dy/y1 天日day/dd/d1 天周week/wk/ww7 天工作日weekday/dw/w1 天小时hour/hh1 小时分钟minute/mi/n1 分钟秒second/ss/s1 秒毫秒millisecond/ms1 毫秒微秒microsecond/mcs1 微秒纳秒nanosecond/ns100 纳秒这张表里有两个坑值得单独拎出来说。第一个坑是m和mi。m是月份mi是分钟。这两个只差一个字母含义差了四万多倍。我在代码评审里见过不止一次有人想加一分钟写成DATEADD(m, 1, ...)跑出来日期直接跳到下个月因为数据量小、现象不明显上线之后才被业务方发现统计口径错了。第二个坑是week和weekday。很多人以为weekday是按工作日算的、会自动跳过周末实际上不是。在 SQL Server 里week是加 7 天weekday是加 1 天两者都只是纯天数平移完全不管这天是不是休息日。如果你想做跳过周末的五个工作日之后得自己写循环或者用日历表去关联别指望weekday帮你办这事。datepart传字符串也可以比如DATEADD(month, 1, ...)会被隐式转换。但我不建议这么写一是没必要多一层转换二是这种写法在部分工具链里会触发语法高亮错误白白浪费排查时间。1.2 第二个参数 number小数、负数与表达式的结果number决定移动多少格。它接收整数有三种使用形态经常被忽略。负数就是往回推。DATEADD(day, -7, ...)拿到的是一周前的同一时刻不需要换DATEDIFF再减也不用DATE_SUB这种别的数据库才有的函数。查近七天的数据时我基本都这么写边界可读性比一堆加减号好很多。小数会被截断不是四舍五入。这是最容易被误解的一处。实测一下SELECT DATEADD(day, 1.9, 2024-01-01) AS a, -- 2024-01-02 DATEADD(day, 1.1, 2024-01-01) AS b, -- 2024-01-02 DATEADD(day, -0.9, 2024-01-01) AS c; -- 2024-01-01三次结果都是往零的方向取整之后再平移。所以如果你在代码里做过总数 / 天数这种除法又把结果直接喂给number小数部分会静默丢失不会报错也不会提醒。想保留精度就得先把单位换成更细的粒度比如把1.5 小时改成DATEADD(minute, 90, ...)。表达式和变量可以直接传。number位置完全可以放一个计算式比如DATEADD(day, n * 2, base)或者放进CASE WHEN里做条件平移。这一点在做根据用户等级给不同的有效期这类逻辑时特别顺手不用在应用层拼字符串。1.3 第三个参数 date返回类型的连锁反应date参数看着最无害其实它决定了返回值的类型进而影响后面所有环节——包括能不能塞进目标列、比较时会不会出隐式转换、显示出来带不带时分秒。规则是这样的DATEADD 的返回类型跟随第三个参数的输入类型。传进去是date出来就是date传进去是datetime2(3)出来还是datetime2(3)精度也保持一致传进去是smalldatetime出来就是smalldatetime而且会被它的精度约束截断到分钟。最容易出事的是字符串字面量。你写DATEADD(day, 1, 2024-01-01)这个2024-01-01并不会被当成date而是按datetime解析的于是结果变成一个带00:00:00.000的datetime。把它插进date类型的列当然没问题但如果你拿它和另一个date列做比较、或者塞进一个varchar列结果就会带上那个多余的零点时间报表上显示出来就不好看。我的习惯是永远显式转换第三个参数写清楚我要的类型DATEADD(day, 1, CAST(2024-01-01 AS date))多敲几个字符省掉后面一堆格式排查。这个习惯我保持了几年确实少踩了不少返工。2. 按时间粒度逐层加一份可直接对照的实操清单把参数讲清楚之后剩下的就是哪种场景该用哪个粒度。这一段我按从大到小的顺序过一遍每个粒度配一个真实的用法场景你可以直接抄去改字段名。2.1 年、季、月跨度最大的三种加法按年推最常见的是算周年日、到期日SELECT DATEADD(year, 1, CAST(2024-03-15 AS date)); -- 2025-03-15闰日是个特殊情况2024-02-29往后推一年会落到2025-02-28函数会自动收敛到该月最后一天不会报错也不会给你一个 2 月 30 日。这个行为在算会员一年有效期时是需要跟业务确认的到底是给到 2 月 28 日还是 3 月 1 日两种口径都有人用函数默认给的是前者。按季度推等价于按月推 3 个月DATEADD(quarter, 1, ...)和DATEADD(month, 3, ...)结果完全一样。既然等价为什么还要留quarter这个关键字主要是为了代码可读性写下个季度的时候用quarter一眼就懂用month, 3还得在脑子里换算一下。团队协作里这点可读性是有价值的。按月推是使用频率最高的一个也是最容易出坑的一个具体见后面第 4 章。这里先给一个典型用法算账单日。-- 假设账期是从每月 15 号开始算下一个账期起始日 SELECT DATEADD(month, 1, CAST(2024-01-15 AS date)); -- 2024-02-152.2 周、日、工作日那个容易被混淆的 wk 与 dw按天推是最直白的SELECT DATEADD(day, 30, CAST(2024-01-01 AS date)); -- 2024-01-31 SELECT DATEADD(day, -1, CAST(2024-03-01 AS date)); -- 2024-02-29注意第二个例子往回推一天自动跨月跨到了闰日函数处理得很好不用你判断上个月有多少天。这也正是我推荐用它而不是自己拼字符串的原因——手写2024-02- (天数-1)这种代码闰年、大小月、跨年全得自己判断出错的概率太高。按周推就是按天推 7 天SELECT DATEADD(week, 2, CAST(2024-01-01 AS date)); -- 2024-01-15它和DATEADD(day, 14, ...)完全等价。有人担心week会受数据库的一周第一天设置影响这个担心放在DATEDIFF上是对的放在DATEADD上就不成立——DATEADD的week是纯粹的 7 天平移不查任何配置。weekday则是 1 天平移和day结果一致。存在这个别名的历史原因我猜是语法完整性实际写代码时基本不会有人用它看到了知道是加一天就行。2.3 时、分、秒、毫秒高频采样场景下的加法往下走到小时以下主要出现在两类场景一类是日志、监控、埋点数据的时间窗口对齐另一类是定时任务的触发时间计算。监控数据经常需要以 5 分钟为粒度聚合这时候可以用DATEADD把任意时间戳对齐到最近的整 5 分钟DECLARE t datetime2 2024-05-20 10:37:42; SELECT DATEADD(minute, DATEDIFF(minute, 0, t) / 5 * 5, 0) AS bucket; -- 2024-05-20 10:35:00这段写法的思路值得说一下DATEDIFF(minute, 0, t)算出从基准点0也就是 1900-01-01到目标时间一共过了多少分钟得到一个整数整数除以 5 再做整除把零头砍掉乘回 5 得到对齐后的分钟数最后用DATEADD把这个分钟数加回基准点还原成一个时间。整个过程没有任何字符串操作全是整数运算效率高而且不会因为格式问题出错。秒和毫秒的用法同理只是粒度更细SELECT DATEADD(second, -30, SYSUTCDATETIME()); -- 30 秒前 SELECT DATEADD(millisecond, 500, t); -- 加半秒微秒和纳秒需要datetime2类型才撑得住精度用datetime会被截断回约 3.33 毫秒的精度这一点在写高精度计时逻辑时必须注意否则你会觉得明明加了 100 微秒怎么没变化。3. 和 DATEDIFF 搭配把 DATEADD 用成日期截断器单独用DATEADD只是平移把它和DATEDIFF组合起来就变成了日期截断和区间构造的利器。这个组合技我认为是日期处理里性价比最高的一个套路掌握之后很多原本要写一堆CASE WHEN的需求都能一行搞定。3.1 构造本月一日零时这类基准点需求里出现频率极高的一类表达是本月一号本周一今天零点。这些都可以用同一个模板套出来DECLARE now datetime2 2024-05-20 14:37:42; -- 今天零点 SELECT DATEADD(day, DATEDIFF(day, 0, now), 0); -- 本月一号零点 SELECT DATEADD(month, DATEDIFF(month, 0, now), 0); -- 今年一月一号零点 SELECT DATEADD(year, DATEDIFF(year, 0, now), 0); -- 本周一零点需配合 SET DATEFIRST 1 SET DATEFIRST 1; SELECT DATEADD(week, DATEDIFF(week, 0, now), 0);模板的骨架是固定的DATEADD(单位, DATEDIFF(单位, 0, 目标时间), 0)。先算出目标时间距基准点差多少个完整单位再把这些单位原样加回基准点多出来的零头自然就被丢掉了。这个写法比CONVERT(varchar(7), now, 120) -01之类的字符串拼法好在哪一是快全是整数运算不用做类型转换二是稳不受语言设置、日期格式设置影响三是能直接参与比较和索引因为结果是标准的日期类型而不是字符串。写这套模板时有个细节要注意基准点0在 SQL Server 里代表1900-01-01。这个值本身没有特殊含义它只是一个干净的、各项都为 0 的起点用它来做差值计算最方便。但如果你的数据里有早于 1900 年的日期DATEDIFF会返回负数这套模板就不适用了需要换成更早的基准比如-0001-01-01。实际业务里极少遇到知道有这么个前提就行。3.2 用半开区间替代 BETWEEN 月末统计月度数据的时候很多人会这么写-- 不推荐 WHERE create_time BETWEEN 2024-05-01 AND 2024-05-31这里藏着两个问题。第一2024-05-31被当成datetime时会补上00:00:00导致 5 月 31 日当天 0 点之后的数据全部漏掉。第二如果create_time是datetime2或者带毫秒边界上还会再丢一截。正确姿势是用左闭右开的半开区间DECLARE start date 2024-05-01; SELECT ... FROM t WHERE create_time start AND create_time DATEADD(month, 1, start);DATEADD(month, 1, start)自动算出下个月一号不用你去查这个月是 28 天、30 天还是 31 天也不用担心闰年。区间左闭右开任何精度的字段都不会漏数据也不会重复统计。这个模式我建议直接固化成团队的编码规范。它的好处不只是正确性还在于扩展性如果要改成按季度统计把month换成quarter改成按年换成year改成按周换成week。整个查询结构一点不用动。3.3 生成连续日期序列的写法做报表时经常需要补齐没有数据的日期比如最近 30 天每天的订单量某几天没有订单也得显示 0。这时候得先造一个连续日期序列出来DATEADD正好可以当生成器用。WITH nums AS ( SELECT TOP (30) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n FROM sys.all_objects ) SELECT DATEADD(day, -n, CAST(GETDATE() AS date)) AS d FROM nums ORDER BY d;思路是先借一张系统表造出 0 到 29 的序号再用DATEADD(day, -n, 今天)逐个往前推得到 30 个日期。TOP (30)控制天数改数字就行。如果环境不允许访问系统表可以用递归 CTE但要注意默认递归上限是 100超过就得加OPTION (MAXRECURSION n)。在我做过的项目里日期序列一般不超过几百行递归完全够用但如果是按分钟生成一整年的序列五十多万行还是老老实实用数字表或者临时表更稳妥。4. 实测中最容易翻车的几处溢出、月末与类型隐式转换函数本身的语法很简单出问题的地方基本都集中在边界条件和类型转换上。这一章把我在实际项目里踩过的、以及帮别人排查过的几类问题整理出来。4.1 月末加法不是简单地加一个月这是DATEADD(month, ...)最反直觉的地方。看下面这组实测SELECT DATEADD(month, 1, CAST(2024-01-31 AS date)); -- 2024-02-29 SELECT DATEADD(month, 1, CAST(2024-02-29 AS date)); -- 2024-03-29 SELECT DATEADD(month, 1, CAST(2024-03-31 AS date)); -- 2024-04-30 SELECT DATEADD(month, -1, CAST(2024-03-31 AS date)); -- 2024-02-29四条结果放在一起看规律就清楚了目标月份没有那么长的天数时结果收敛到目标月的最后一天但一旦收敛过你再往后加起点就变成了收敛后的日期不会自动弹回原来的月末。第二行最能说明问题2024-02-29加一个月得到2024-03-29而不是2024-03-31。也就是说这种加法不具备往返一致性——先加一个月再减一个月不保证回到原点SELECT DATEADD(month, -1, DATEADD(month, 1, CAST(2024-01-31 AS date))); -- 2024-01-29不是 2024-01-31这个特性在算分期、算周期性账单的时候会积累误差。比如每月 31 号扣款到了 2 月变成 29 号3 月又变成 29 号之后一路都是 29 号永远回不到 31 号。如果业务上要求始终是月末正确做法是用锚点日期加序号-- 以每月 1 号为锚点先加月份再减一天得到目标月的最后一天 SELECT DATEADD(day, -1, DATEADD(month, n 1, CAST(2024-01-01 AS date)));这样每一次计算都从固定的2024-01-01出发误差不会累积。这个技巧我在做账期系统时用过确实比直接加月份靠谱。4.2 边界溢出的报错长什么样date、datetime、smalldatetime各自的取值范围不一样加超了会直接抛错不是返回 null也不是悄悄截断SELECT DATEADD(year, 1, CAST(9999-12-31 AS date)); -- 报错将值添加到 date 列导致溢出。datetime的下界更靠前往回推太远也会炸SELECT DATEADD(year, -1, CAST(1753-01-01 AS datetime)); -- 报错将值添加到 datetime 列导致溢出。这类错误在生产环境里通常出现在两种地方一是用户输入了极端的日期值而前端没做校验二是循环里用累加的方式推日期结果越推越远。第二种情况尤其隐蔽因为循环跑到第 9000 次才炸前面都好好的。防御办法很简单在关键路径上加一层范围判断CASE WHEN base 1901-01-01 THEN NULL ELSE DATEADD(year, 100, base) END或者在应用层就把输入限制在一个合理区间内。我倾向于两者都做数据库层兜底应用层给用户友好提示。4.3 把函数套在索引列上性能是怎么掉的这条是我见过代价最大的一个误用。写法大概长这样-- 索引失效 WHERE DATEADD(day, 1, create_time) 2024-01-01 -- 索引用得上 WHERE create_time DATEADD(day, -1, 2024-01-01)create_time上有索引的情况下第一种写法会让索引彻底用不上因为优化器没法为加了 1 天之后的值去索引里定位只能全表扫描逐行算。数据量几十万的时候看不出来上千万之后差别就是秒级和分钟级的区别。判断标准很朴素函数套在列上索引就废函数套在常量上索引照用。所以做日期平移的时候能挪到等号右边就挪过去。上面第二个写法把-1挪到了常量侧等价变换之后索引完好。如果实在没法挪比如两边都是列那就得考虑持久化计算列ALTER TABLE t ADD next_day AS DATEADD(day, 1, create_time) PERSISTED; CREATE INDEX IX_next_day ON t (next_day);DATEADD是确定性函数可以做持久化计算列并建索引。这个方案在需要频繁按偏移后的日期过滤时很有效代价是写入时多维护一个索引以及表结构变得更复杂。用不用取决于读写比例写多读少的表我不建议加。5. 换个数据库怎么写DATEADD 在不同方言里的对应物DATEADD这个名字严格来说不是 SQL 标准它更像 SQL Server 家族的方言。同一个需求搬到别的数据库上写法差异不小做迁移的时候这一块特别容易漏改。下面按常见的几种库做个对照。5.1 MySQL 的 DATE_ADD 与 INTERVAL 语法MySQL 没有DATEADD对应的是DATE_ADD(date, INTERVAL expr unit)SELECT DATE_ADD(2024-01-31, INTERVAL 1 MONTH); -- 2024-02-29 SELECT DATE_ADD(2024-01-01, INTERVAL -7 DAY); -- 2023-12-25 SELECT DATE_SUB(2024-01-01, INTERVAL 7 DAY); -- 同上翻写的时候有几个差异必须记住单位的位置不一样。SQL Server 是DATEADD(month, 1, d)单位在前MySQL 是DATE_ADD(d, INTERVAL 1 MONTH)单位和数值一起放在后面。写过一次就不会忘但批量迁移脚本里如果靠正则替换很容易搞错。单位名字大小写不敏感但单复数有区别。DAY和DAYS都行MONTH和MONTHS都行习惯上单数更常见。月末行为基本一致2024-01-31加一个月同样返回2024-02-29不会报错。这一点两边是对齐的迁移时不用额外处理。DATE_SUB是DATE_ADD加负数的一个语法糖语义上完全等价选哪个看团队习惯。5.2 PostgreSQL 的 interval 运算PostgreSQL 走的是另一条路——把时间间隔当成一等公民用interval字面量做加减SELECT DATE 2024-01-31 INTERVAL 1 month; -- 2024-02-29 SELECT DATE 2024-01-01 - INTERVAL 7 days; -- 2023-12-25 SELECT TIMESTAMP 2024-01-01 10:00 INTERVAL 90 minutes;也可以把数值和单位分开写方便把变量塞进去SELECT DATE 2024-01-01 (n || days)::interval FROM t; -- 或者更规范一点 SELECT DATE 2024-01-01 make_interval(days n) FROM t;这里有个容易踩的点PostgreSQL 的interval 1 month和interval 30 days不是一回事。前者按日历月推进后者是固定 30 天跨月的时候结果会不一样。写月度统计的时候必须用1 month用30 days会在 2 月出偏差。这个坑我在做跨库口径对比时踩过两边查同样的条件结果差了一天查了半天才发现是间隔单位的问题。5.3 Oracle 与 SQLite 的替代写法Oracle 没有专门的日期加减函数它是用算术运算符直接实现的-- 加 1 天 SELECT DATE 2024-01-01 1 FROM dual; -- 加 1 个月用专门的 ADD_MONTHS SELECT ADD_MONTHS(DATE 2024-01-31, 1) FROM dual; -- 2024-02-29 -- 加 1 小时1/24 天 SELECT DATE 2024-01-01 1/24 FROM dual; n的单位是天所以加小时、加分钟都得换算成分数。这种写法看着简洁但1/24、1/1440这类魔数散在代码里读起来很吃力我通常在项目里包一层自定义函数把语义显式化。ADD_MONTHS的月末行为和DATEADD(month, ...)一致也是收敛到最后一天。但要注意 Oracle 的DATE类型自带时分秒做日期比较时同样要用半开区间逻辑和前面讲的一样。SQLite 更简单它只有文本形式的日期全靠字符串函数SELECT date(2024-01-31, 1 month); -- 2024-02-29 SELECT datetime(2024-01-01 10:00, -30 minutes); SELECT date(now, 7 days);修饰符的写法是符号 数值 单位单位用复数英文单词。SQLite 没有真正的日期类型比较和排序都依赖 ISO 格式字符串的字典序所以格式必须是YYYY-MM-DD或者YYYY-MM-DD HH:MM:SS写成别的格式就全乱了。把这几种方言放在一张表里对照迁移时对照着改效率最高需求SQL ServerMySQLPostgreSQLOracleSQLite加 1 天DATEADD(day,1,d)DATE_ADD(d,INTERVAL 1 DAY)d INTERVAL 1 dayd 1date(d,1 day)加 1 个月DATEADD(month,1,d)DATE_ADD(d,INTERVAL 1 MONTH)d INTERVAL 1 monthADD_MONTHS(d,1)date(d,1 month)减 30 分钟DATEADD(minute,-30,d)DATE_ADD(d,INTERVAL -30 MINUTE)d - INTERVAL 30 mind - 30/1440datetime(d,-30 minutes)今天零点DATEADD(day,DATEDIFF(day,0,d),0)CURDATE()d::dateTRUNC(d)date(now)最后一行值得单独注意SQL Server 里取今天零点需要那个DATEDIFF组合拳而其他几家基本都有一行搞定的写法。这算是 SQL Server 在日期处理上一个不太方便的地方但用熟了模板之后也就是肌肉记忆的事。我在实际做跨库项目时的体会是别指望一套 SQL 走遍所有数据库。日期这块尤其如此各家在月末怎么算interval 是不是日历月有没有原生日期类型这几个点上的取舍完全不同硬要统一往往会在边界上出问题。比较务实的做法是在数据访问层做一层薄封装把日期加减抽象成方法底层按目标库分别实现业务代码只调方法不直接写函数名。这样即使后面换库改动量也控制在很小的范围里。另外再分享一个小技巧写日期相关的 SQL 时先在测试库里把月末、闰日、跨年这几个边界值都跑一遍把这些用例固化成单元测试比事后排查生产数据要省心得多。