SQLServer自定义函数：fnDateInterval-数据库

/*****************************************
** 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
  取得两个时间的间隔，用“XX年XX月XX日”来表示。
** Return values: varchar(100) 
** Parameters: @dtmStart,@dtmEnd
** Author: 何毅翔(MyGodness)
** 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)
As
Begin
declare @chvInterval varchar(100)
select @chvInterval=convert(varchar(10),@dtmStart,120)+'距离'+convert(varchar(10),@dtmEnd,120)

--如果开始日期晚于结束日期
declare @dtmTemp datetime
if @dtmStart>@dtmEnd
 select @dtmTemp=@dtmStart,@dtmStart=@dtmEnd,@dtmEnd=@dtmTemp,@chvInterval=@chvInterval+'已经过了'
else
 select @chvInterval=@chvInterval+'还有'

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 --开始日期下个月的第一天
declare @dtmEnd2 datetime --结束日期当月的第一天
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 --开始日期当月的天数
select @dtmDaysOfStart=datediff(d,dateadd(mm,datediff(mm,0,@dtmStart),0),dateadd(mm,datediff(mm,0,@dtmStart)+1,0))

declare @dtmDaysOfEnd int --结束日期当月的天数
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)+'年'
if @m=0
 select @chvM=''
else
 select @chvM=cast(@m as varchar)+'个月'
if @d=0
 select @chvD=''
else
 select @chvD=cast(@d as varchar)+'天'
if @y=0 and @m=0 and @d=0
 select @chvD='0天'

select @chvInterval=@chvInterval+@chvY+@chvM+@chvD

return @chvInterval
End