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

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


--实际差12月+30天
select DATEDIFF(M,'2013-08-01','2014-08-31') AS [12],'2014-08-31') AS [395],'2014-08-31')/30 AS [13],'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','DAY') OR DATEDIFF(D,@startDatetime,@endDatetime)<0
	RETURN -1

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

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

return @RETURN
END

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

结果：
2
2
1	

*/

CREATE PROCEDURE DBO.getDateDiffKK(@Interval varchar(10),@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',@endDatetime)
 END

SELECT @RETURN
END

/*

EXEC getDateDiffKK 'Y','2015-11-11' 
EXEC getDateDiffKK 'M','2015-11-11' 
EXEC getDateDiffKK 'D','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,@JSRQ)) end  AS 月份数,CASE WHEN DAY(@QSRQ) <= DAY(@JSRQ) THEN DATEDIFF(D,@JSRQ),@QSRQ),@JSRQ)
	ELSE DATEDIFF(D,@JSRQ)),@JSRQ) end  AS 天数
/*
结果：
月份数	天数
26		19

日期差26个月零19天
*/

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!