计算两个时间相差多少年月日的sql算法

答案:3 悬赏:80 手机版

解决时间 2021-02-19 09:47

提问者网友：城市野鹿
2021-02-18 15:46

计算两个时间相差多少年月日的sql算法

最佳答案

五星知识达人网友：轻雾山林
2021-02-18 16:58

如：起始时间 2011-1-1 截止日期 2011-2-1 两个时间比较相差 0年1个月如：起始时间 2010-11-1 截止日期 2011-12-1 两个时间比较相差 1年1个月怎么比较才能得出~ 相差多少年多少个月这个当然很简单, 一句SQL就可以了：select ltrim(datediff(yy,@dt1,@dt2))+'年'+ltrim(datediff(mm,@dt1,@dt2)%12)+'月'
但是如果连几天也要计算呢？闲着没事自己随便在SQL Server下写了写，store procedures代码如下，应该没可以了：CREATE PROCEDURE GetDateDiff
@fromDate nvarchar(10),
@toDate nvarchar(10)
AS

DECLARE @YearDiff INTEGER
DECLARE @MonthDiff INTEGER
DECLARE @DayDiff INTEGER
DECLARE @ToMonth INTEGER
DECLARE @FromDay INTEGER
DECLARE @ToDay INTEGER

SET @YearDiff = LTRIm(DATEDIFF(yy, @fromDate, @toDate))
SET @MonthDiff = LTRIm(DATEDIFF(mm, @fromDate, @toDate)%12)
SET @ToMonth = Month(cast(@toDate as Datetime))
SET @FromDay = Day(cast(@fromDate as Datetime))
SET @ToDay = Day(cast(@ToDate as Datetime))
IF (@ToDay - @FromDay) < 0
BEGIN
IF (@ToMonth - 1) = 1 OR (@ToMonth - 1) = 3 OR (@ToMonth - 1) = 5 OR (@ToMonth - 1) = 7 OR (@ToMonth - 1) = 8 OR (@ToMonth - 1) = 10 OR (@ToMonth - 1) = 12
BEGIN
SET @DayDiff = 31 + @Today - @FromDay
SET @MonthDiff = @MonthDiff -1
END
ELSE
BEGIN
SET @DayDiff = 30 + @Today - @FromDay
SET @MonthDiff = @MonthDiff -1
END
END
ELSE
BEGIN
SET @DayDiff = @Today - @FromDay
END

SELECT CAST(@YearDiff AS NVARCHAr(10)) + '年' + CAST(@MonthDiff AS NVARCHAr(10)) + '月' + CAST(@DayDiff AS NVARCHAr(10)) + '日'

全部回答

1楼网友：长青诗
2021-02-18 19:07

SELECT CAST(@YearDiff AS NVARCHAr(10)) + '年' + CAST(@MonthDiff AS NVARCHAr(10)) + '月' + CAST(@DayDiff AS NVARCHAr(10)) + '日'！

2楼网友：英雄的欲望
2021-02-18 18:22

用datediff()来就够了，要啥算法啊

我要举报

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息