/*************************************** **********************
** File: fndateinterval. SQL
** Name: fndateinterval
** Description: Get the interval between the start date and the end date.
Use the format as "number years and number months and number days" to express the interval
The interval between the two time periods is expressed by "XX Year XX month xx day.
** Return values: varchar (100)
** Parameters: @ dtmstart, @ dtmend
** Author: mygodness)
** Date: 2005-3-31
** Use method: Select DBO. fndateinterval ('2017-2-18 ', '2017-4-6 ')
**************************************** *********************/
Create Function fndateinterval (@ dtmstart datetime, @ dtmend datetime)
Returns varchar (100)
As
Begin
Declare @ chvinterval varchar (100)
Select @ chvinterval = convert (varchar (10), @ dtmstart, 120) + ''+ convert (varchar (10), @ dtmend, 120)
-- If the start date is later than the end date
Declare @ dtmtemp datetime
If @ dtmstart> @ dtmend
Select @ dtmtemp = @ dtmstart, @ dtmstart = @ dtmend, @ dtmend = @ dtmtemp, @ chvinterval = @ chvinterval + 'already exists'
Else
Select @ chvinterval = @ chvinterval + 'and'
Declare @ Y int, @ M int, @ d int
Select @ Y = datediff (YY, @ dtmstart, @ dtmend)
If datediff (D, dateadd (YY, @ Y, @ dtmstart), @ dtmend) <= 0
Select @ Y = @ Y-1
Select @ M = datediff (M, @ dtmstart, @ dtmend)-@ Y * 12-1
Declare @ dtmstart2 datetime -- start date, the first day of the next month
Declare @ dtmend2 datetime -- the first day of the end date of the current 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 -- number of days from the start date to the current month
Select @ dtmdaysofstart = datediff (D, dateadd (mm, datediff (mm, 0, @ dtmstart), 0), dateadd (mm, datediff (mm, 0, @ dtmstart) + 1, 0 ))
Declare @ dtmdaysofend int -- number of days from the end date to the current 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 + 1
If @ 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) + 'day'
If @ Y = 0 and @ M = 0 and @ D = 0
Select @ chvd = '0 days'
Select @ chvinterval = @ chvinterval + @ chvy + @ chvm + @ chvd
Return @ chvinterval
End