Contents



create function dbo.fnDateDiff(@stdt datetime, @endt datetime)
    returns varchar(12)
as
begin
    declare @rdt varchar(12)
    declare @y int, @m int, @d int
    declare @diffmonth int
 
    set @diffmonth = datediff(mm, @stdt, @endt)
    if day(@endt) >= day(@stdt) - 1
        begin
            set @y = @diffmonth / 12
            set @m = @diffmonth % 12
            set @d = day(@endt) - day(@stdt) + 1
        end
    else
        begin
            set @y = @diffmonth / 12
            if @diffmonth % 12 = 0
                begin
                    set @y = @y - 1
                    set @m = @diffmonth % 12 - 1 + 12
                end
            else
                begin
                    set @m = @diffmonth % 12 - 1
                end
            set @d = day(@endt) - day(@stdt) + 1 + 
                     case when month(@endt) in (1,3,5,7,8,10,12) then 31
                          when month(@endt) in (4,6,9,11) then 30
                          when month(@endt) = 2 and year(@endt) % 4 = 0 then 29
                          else  28
                     end
        end
 
    set @rdt = right('00'+convert(varchar(2), @y),2) + '' +
               right('00'+convert(varchar(2), @m),2) + '' +
               right('00'+convert(varchar(2), @d),2) + ''
    return (@rdt)
end
go
 
 
---ろ
 
declare @st datetime
declare @en datetime
 
set @st = '1999-11-05'
set @en = '2004-01-31'
 
select dbo.fnDateDiff(@st, @en)