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


     1 /****** Object:  StoredProcedure [dbo].[GetDateDiff]    Script Date: 11/12/2019 16:14:05 ******/
     2 SET ANSI_NULLS ON
     3 GO
     4 SET QUOTED_IDENTIFIER ON
     5 GO
     6 --计算两个时间相差多少年月日的sql算法,用于新生儿年龄计算
     7 ALTER PROCEDURE [dbo].[GetDateDiff]
     8     @fromDate NVARCHAR(10) ,--开始日期
     9     @toDate NVARCHAR(10)    --结束日期
    10 AS 
    11     DECLARE @YearDiff INTEGER
    12     DECLARE @MonthDiff INTEGER
    13     DECLARE @DayDiff INTEGER
    14     DECLARE @ToMonth INTEGER
    15     DECLARE @FromDay INTEGER
    16     DECLARE @ToDay INTEGER
    17 
    18 
    19     SET @YearDiff = LTRIM(DATEDIFF(yy, @fromDate, @toDate))
    20     SET @MonthDiff = LTRIM(DATEDIFF(mm, @fromDate, @toDate) % 12)
    21     SET @ToMonth = MONTH(CAST(@toDate AS DATETIME))
    22     SET @FromDay = DAY(CAST(@fromDate AS DATETIME))
    23     SET @ToDay = DAY(CAST(@ToDate AS DATETIME))
    24     IF ( @ToDay - @FromDay ) < 0 
    25         BEGIN
    26             IF ( @ToMonth - 1 ) = 1
    27                 OR ( @ToMonth - 1 ) = 3
    28                 OR ( @ToMonth - 1 ) = 5
    29                 OR ( @ToMonth - 1 ) = 7
    30                 OR ( @ToMonth - 1 ) = 8
    31                 OR ( @ToMonth - 1 ) = 10
    32                 OR ( @ToMonth - 1 ) = 12 
    33                 BEGIN
    34                     SET @DayDiff = 31 + @Today - @FromDay
    35                     SET @MonthDiff = @MonthDiff - 1
    36                 END    
    37             ELSE 
    38                 BEGIN
    39                     SET @DayDiff = 30 + @Today - @FromDay
    40                     SET @MonthDiff = @MonthDiff - 1
    41                 END
    42         END
    43     ELSE 
    44         BEGIN
    45             SET @DayDiff = @Today - @FromDay
    46         END
    47         
    48     SELECT  CAST(@YearDiff AS NVARCHAR(10)) + ''
    49             + CAST(@MonthDiff AS NVARCHAR(10)) + '个月'
    50             + CAST(@DayDiff AS NVARCHAR(10)) + ''        
    51   
  • 相关阅读:
    信协第一学期考核
    20175323《Java程序设计》第五周学习总结
    20175323《Java程序设计》第四周学习总结
    20175323《Java程序设计》第三周学习总结
    20175323《Java程序设计》第二周学习总结
    20175323 第一周学习总结
    第一次个人编程作业
    第一次博客作业
    团队项目-选题报告
    第一次结对编程作业
  • 原文地址:https://www.cnblogs.com/AlbertSmith/p/11896723.html
Copyright © 2020-2023  润新知