sqlserver中返回两个日期间相差几年零几月零几天，DATEDIFF通常不准确

原创

JeesonHuang 2023-01-11 02:33:45 博主文章分类：SQLServer ©著作权

©著作权归作者所有：来自51CTO博客作者JeesonHuang的原创作品，请联系作者获取转载授权，否则将追究法律责任

--实际差12月+1天
select DATEDIFF(M,'2013-08-31','2014-09-01') as [13]
,DATEDIFF(D,'2013-08-31','2014-09-01') AS [366]
,DATEDIFF(D,'2013-08-31','2014-09-01')/30 AS [12]
,DATEDIFF(D,'2013-08-31','2014-09-01')%30 AS [6]


--实际差12月+30天
select DATEDIFF(M,'2013-08-01','2014-08-31') AS [12]
,DATEDIFF(D,'2013-08-01','2014-08-31') AS [395]
,DATEDIFF(D,'2013-08-01','2014-08-31')/30 AS [13]
,DATEDIFF(D,'2013-08-01','2014-08-31')%30 AS [5]

/*

 返回两个日期间相差几年零几月零几天
 @interval IN ('Y','YEAR','M','MONTH','D','DAY')
 
 不知道为什么，代码一样，存储过程正确,函数的不正确！求解！！！！

*/
CREATE FUNCTION DBO.getDateDiff(@Interval varchar(10),@startDate DATETIME,@endDate DATETIME)
RETURNS VARCHAR
AS 
BEGIN
 DECLARE @RETURN VARCHAR(10)
 DECLARE @startDatetime DATETIME
 DECLARE @endDatetime DATETIME
 SET @startDatetime = @startDate
 SET @endDatetime = @endDate

 IF @Interval NOT IN('Y','YEAR','M','MONTH','D','DAY') OR DATEDIFF(D,@startDatetime,@endDatetime)<0
  RETURN -1

 --返回相差年份 
 IF @interval IN ('Y','YEAR')
  SET @RETURN = DATEDIFF(D,@startDatetime,@endDatetime)/365
   
 --返回相差年份后相差的月份 
 IF @Interval IN ('M','MONTH')
 BEGIN
  SET @startDatetime = DATEADD(YEAR,DATEDIFF(D,@startDatetime,@endDatetime)/365,@startDatetime)
  IF DAY(@startDatetime) <= DAY(@endDatetime)
    SET @RETURN = DATEDIFF(M,@startDatetime,@endDatetime)
  ELSE
    SET @RETURN = DATEDIFF(M,@startDatetime,DATEADD(M,-1,@endDatetime))
 END

 --返回相差月份后相差的天数
 IF @Interval IN ('D','DAY')
 BEGIN
  SET @startDatetime = DATEADD(YEAR,DATEDIFF(D,@startDatetime,@endDatetime)/365,@startDatetime)
  IF DAY(@startDatetime) <= DAY(@endDatetime)
    SET @startDatetime = DATEADD(M,DATEDIFF(M,@startDatetime,@endDatetime),@startDatetime)
  ELSE
    SET @startDatetime = DATEADD(M,DATEDIFF(M,@startDatetime,DATEADD(M,-1,@endDatetime)),@startDatetime)
  SET @RETURN = DATEDIFF(D,@startDatetime,@endDatetime)
 END

return @RETURN
END

/*
SELECT DBO.getDateDiff('Y','2013-08-23','2015-11-11')
SELECT DBO.getDateDiff('M','2013-08-23','2015-11-11')
SELECT DBO.getDateDiff('D','2013-08-23','2015-11-11')

结果：
2
2
1 

*/

CREATE PROCEDURE DBO.getDateDiffKK(@Interval varchar(10),@startDate DATETIME,@endDate DATETIME)
AS 
BEGIN
 DECLARE @RETURN VARCHAR(10)
 DECLARE @startDatetime DATETIME
 DECLARE @endDatetime DATETIME
 SET @startDatetime = @startDate
 SET @endDatetime = @endDate

 IF @Interval NOT IN('Y','YEAR','M','MONTH','D','DAY') OR DATEDIFF(D,@startDatetime,@endDatetime)<0
  RETURN -1

 --返回相差年份 
 IF @interval IN ('Y','YEAR')
  SET @RETURN = DATEDIFF(D,@startDatetime,@endDatetime)/365
   
 --返回相差年份后相差的月份 
 IF @Interval IN ('M','MONTH')
 BEGIN
  SET @startDatetime = DATEADD(YEAR,DATEDIFF(D,@startDatetime,@endDatetime)/365,@startDatetime)
  IF DAY(@startDatetime) <= DAY(@endDatetime)
    SET @RETURN = DATEDIFF(M,@startDatetime,@endDatetime)
  ELSE
    SET @RETURN = DATEDIFF(M,@startDatetime,DATEADD(M,-1,@endDatetime))
 END

 --返回相差月份后相差的天数
 IF @Interval IN ('D','DAY')
 BEGIN
  SET @startDatetime = DATEADD(YEAR,DATEDIFF(D,@startDatetime,@endDatetime)/365,@startDatetime)
  IF DAY(@startDatetime) <= DAY(@endDatetime)
    SET @startDatetime = DATEADD(M,DATEDIFF(M,@startDatetime,@endDatetime),@startDatetime)
  ELSE
    SET @startDatetime = DATEADD(M,DATEDIFF(M,@startDatetime,DATEADD(M,-1,@endDatetime)),@startDatetime)
  SET @RETURN = DATEDIFF(D,@startDatetime,@endDatetime)
 END

SELECT @RETURN
END

/*

EXEC getDateDiffKK 'Y','2013-08-23','2015-11-11' 
EXEC getDateDiffKK 'M','2013-08-23','2015-11-11' 
EXEC getDateDiffKK 'D','2013-08-23','2015-11-11' 
结果：
2
2
19

*/

--选择时计算结果：

DECLARE @QSRQ VARCHAR(10)
DECLARE @JSRQ VARCHAR(10)
SET @QSRQ ='2013-08-23'
SET @JSRQ ='2015-11-11'
SELECT
  CASE WHEN DAY(@QSRQ) <= DAY(@JSRQ) THEN DATEDIFF(M,@QSRQ,@JSRQ)
  ELSE DATEDIFF(M,@QSRQ,DATEADD(M,-1,@JSRQ)) end  AS 月份数
,CASE WHEN DAY(@QSRQ) <= DAY(@JSRQ) THEN DATEDIFF(D,DATEADD(M,DATEDIFF(M,@QSRQ,@JSRQ),@QSRQ),@JSRQ)
  ELSE DATEDIFF(D,DATEADD(M,DATEDIFF(M,@QSRQ,DATEADD(M,-1,@JSRQ)),@QSRQ),@JSRQ) end  AS 天数
/*
结果：
月份数 天数
26    19

日期差26个月零19天
*/

--错误：
select DATEDIFF(M,@QSRQ,@JSRQ) --26
select DATEDIFF(D,@QSRQ,@JSRQ) --810

2016-08-30 补充 -------------------------------

ALTER FUNCTION DBO.getDateDiff(@startDate DATETIME,@endDate DATETIME)  
RETURNS VARCHAR(10) --长度设置
AS   
BEGIN  
  RETURN DATEDIFF(D,@startDate,@endDate)
END
GO

ALTER FUNCTION DBO.getDateDiff(@startDate DATETIME,@endDate DATETIME)  
RETURNS INT  --返回整形
AS   
BEGIN  
  RETURN DATEDIFF(D,@startDate,@endDate)
END
GO


--存储过程：正确
ALTER PROCEDURE DBO.getDateDiffKK(@startDate DATETIME,@endDate DATETIME)  
AS   
BEGIN  
  SELECT DATEDIFF(D,@startDate,@endDate)  
END  
GO

--执行结果：
SELECT DBO.getDateDiff('2013-08-23','2015-11-11') 
GO
EXEC DBO.getDateDiffKK '2013-08-23','2015-11-11'
GO