DATEDIFF 函数基础语法DATEDIFF 函数用于计算两个日期之间的差值基本语法如下DATEDIFF(datepart,startdate,enddate)datepart指定计算差值的单位如天、月、年等startdate起始日期enddate结束日期支持的日期单位常见 datepart 参数值day或dd天数差month或mm月数差year或yyyy年数差week或wk周数差hour或hh小时差minute或mi分钟差second或ss秒数差典型使用场景计算员工入职天数SELECTemployee_name,DATEDIFF(day,hire_date,GETDATE())ASdays_employedFROMemployees;计算订单处理时长小时SELECTorder_id,DATEDIFF(hour,order_date,ship_date)ASprocessing_hoursFROMorders;跨数据库实现差异MySQL/MariaDBDATEDIFF(enddate,startdate)-- 仅返回天数差SQL ServerDATEDIFF(datepart,startdate,enddate)Oracleenddate-startdate-- 天数差MONTHS_BETWEEN(enddate,startdate)-- 月数差PostgreSQLDATE_PART(day,enddate-startdate)高级用法示例计算年龄精确到年SELECTname,DATEDIFF(year,birth_date,GETDATE())-CASEWHENDATEADD(year,DATEDIFF(year,birth_date,GETDATE()),birth_date)GETDATE()THEN1ELSE0ENDASexact_ageFROMpersons;计算工作日天数排除周末SELECTstart_date,end_date,DATEDIFF(day,start_date,end_date)-DATEDIFF(week,start_date,end_date)*2-CASEWHENDATEPART(weekday,start_date)1THEN1ELSE0ENDCASEWHENDATEPART(weekday,end_date)1THEN1ELSE0ENDASwork_daysFROMprojects;性能优化建议在 WHERE 子句中使用 DATEDIFF 时避免对列进行函数计算-- 不推荐SELECT*FROMordersWHEREDATEDIFF(day,order_date,GETDATE())30;-- 推荐SELECT*FROMordersWHEREorder_dateDATEADD(day,-30,GETDATE());对大表查询时考虑在计算列上建立索引ALTERTABLEordersADDCOLUMNprocessing_daysASDATEDIFF(day,order_date,ship_date);CREATEINDEXidx_processing_daysONorders(processing_days);常见错误处理处理 NULL 值SELECTDATEDIFF(day,COALESCE(start_date,1900-01-01),end_date)FROMtime_records;日期格式验证SELECTDATEDIFF(day,TRY_CONVERT(datetime,start_date_string),end_date)FROMstring_dates;特殊场景解决方案计算闰年天数差SELECTDATEDIFF(day,2020-02-28,2020-03-01)ASleap_year_diff;-- 返回2处理时区差异SELECTDATEDIFF(hour,SWITCHOFFSET(start_datetime,-08:00),SWITCHOFFSET(end_datetime,-05:00))FROMglobal_events;