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