/ ************************************************** ************** File: fndateInterval.sql ** name: fndateInterval ** Description: get the interval. Use the format as "Number Years and Number Months And Number Days "TO Express the interval acquires the interval between two times, expressed in" XX year XX Yue XX Day ". ** Return Values: VARCHAR (100) ** parameters: @ dtmstart, @ DTMEND ** Author: 毅 翔 ("2005-2) ** Date: 2005-3-31 ** USE Method: Select DBo.fndateInterval ('2005-2- 18 ',' 2005-4-6 ') ******************************************************** ********************* /
Create function fnDateInterval (@dtmStart datetime, @ dtmEnd datetime) returns varchar (100) AsBegindeclare @chvInterval varchar (100) select @ chvInterval = convert (varchar (10), @ dtmStart, 120) 'from' convert (varchar (10 ), @ DTMEND, 120)
- If the start date is later than the end date declare @dtmTemp datetimeif @dtmStart> @dtmEnd select @ dtmTemp = @ dtmStart, @ dtmStart = @ dtmEnd, @ dtmEnd = @ dtmTemp, @ chvInterval = @ chvInterval 'has been a' else select @ Chvinterval = @ chainterval 'has'
Declare @y int, @ m int, @ D INTSELECT @ y = datediff (yy, @ dtmstart, @ dtmend) if Datediff (d, dateadd (@ y, @ dtmend), @ dtmend) <= 0 Select @Y = @ Y-1Select @ m = datediff (m, @ dtmstart, @ dtmend) - @ y * 12-1
Declare @ dtmstart2 datetime - The first day of the start date next day Declare @ dtmend2 datetime - End Date The first day of the month Select @ dtmstart2 = dateadd (mm, datediff (mm, 0, @ dtmstart) 1,0) SELECT @ dtmend2 = dateadd (mm, datediff (mm, 0, @ dtmend), 0)
Select @ d = datediff (d, @ dtmstart, @ dtmstart2) -datediff (d, @ dtmend, @ DTMEND2)
Declare @dtmdaysofstart int - Start date The day of the day SELECT @ DTMDaysofstart = Datediff (D, Dateadd (mm, Datediff (mm, 0, @ dtmstart), 0), DateAdd (mm, datediff (mm, 0, @ dtmstart) 1, 0)) Declare @dtmdaysofend Int - End Date The number of days in the month Select @ dtmdaysofend = Datediff (D, DateAdd (mm, Datediff (mm, 0, @ dtmend), 0), DateAdd (mm, datediff (mm, 0 , @ DTMEND) 1, 0))
IF @dtmdaysofstart <= @ D SELECT @ D = @ D- @ DTMDaysofStart, @ m = @ m 1if @ m = 12 Select @ m = 0, @ y = @ y 1
Declare @chvy varchar (20), @ chVM varchar (20), @ chvd varchar (20) if @ y = 0 select @Chvy = 'Else select @ chvy = Cast (@y as varchar) ' year 'IF @ m = 0 Select @CHVM = 'Else SELECT @ CHVM = CAST (@m as varchar) ' month 'if @ D = 0 Select @CHVD =' 'else select @ CHVD = CAST (@d as varchar) '天 i @ y = 0 and @ m = 0 and @ D = 0 Select @ CHVD =' 0 days'
SELECT @ chainterval = @ chainterval @ chvy @ CHVM @ CHVD
Return @chvintervalend
