/*****************************************
** 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