Technical Article

DateDiff2 Function

,

A Modified Version of dateDiff. 
DateDiff Fucntion checks the "datepart" only of the dates.
This modified function returns how many "date part" is exactly passed.

Use Master
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.DateDiff2') 
and xtype in (N'FN', N'IF', N'TF'))
drop function dbo.DateDiff2
GO

CREATE Function DateDiff2 (  
 @DatePart Varchar(20), @D1 dateTime, @D2 DateTime)  
Returns Int  
AS  
BEGIN  
Declare @Diff int, @Date1 DateTime, @Date2 dateTime
IF @D1>@D2
 Select @Date1=@D2, @Date2=@D1
Else
 Select @Date1=@D1, @Date2=@D2


IF @DatePart In ('Year', 'yy', 'yyyy') 
BEGIN
Select @Diff = DateDiff(Year, @Date1, @Date2)
IF DateAdd(Year, @Diff, @Date1)>@Date2
Set @Diff= @Diff-1
END
ELSE 
IF @DatePart In ('Quarter', 'qq', 'q') 
BEGIN
Select @Diff = DateDiff(Quarter, @Date1, @Date2)
IF DateAdd(Quarter, @Diff, @Date1)>@Date2
Set @Diff= @Diff-1
END
ELSE 
IF @DatePart In ('Month', 'mm', 'm') 
BEGIN
Select @Diff = DateDiff(Month, @Date1, @Date2)
IF DateAdd(Month, @Diff, @Date1)>@Date2
Set @Diff= @Diff-1
END
ELSE 
IF @DatePart In ('DayofYear', 'dy', 'y') 
BEGIN
Select @Diff = DateDiff(DayOFYear, @Date1, @Date2)
IF DateAdd(DayOFYear, @Diff, @Date1)>@Date2
Set @Diff= @Diff-1
END
ELSE 
IF @DatePart In ('Day', 'dd', 'd') 
BEGIN
Select @Diff = DateDiff(Day, @Date1, @Date2)
IF DateAdd(Day, @Diff, @Date1)>@Date2
Set @Diff= @Diff-1
END
ELSE 
IF @DatePart In ('Week', 'wk', 'ww') 
BEGIN
Select @Diff = DateDiff(Week, @Date1, @Date2)
IF DateAdd(Week, @Diff, @Date1)>@Date2
Set @Diff= @Diff-1
END
ELSE 
IF @DatePart In ('WeekDay', 'dw') 
BEGIN
Select @Diff = DateDiff(WeekDay, @Date1, @Date2)
IF DateAdd(WeekDay, @Diff, @Date1)>@Date2
Set @Diff= @Diff-1
END
ELSE 
IF @DatePart In ('Hour', 'hh') 
BEGIN
Select @Diff = DateDiff(Hour, @Date1, @Date2)
IF DateAdd(Hour, @Diff, @Date1)>@Date2
Set @Diff= @Diff-1
END
ELSE 
IF @DatePart In ('Minute', 'mi', 'n') 
BEGIN
Select @Diff = DateDiff(Minute, @Date1, @Date2)
IF DateAdd(Minute, @Diff, @Date1)>@Date2
Set @Diff= @Diff-1
END
ELSE 
IF @DatePart In ('Second', 'ss', 's') 
BEGIN
Select @Diff = DateDiff(Second, @Date1, @Date2)
IF DateAdd(Second, @Diff, @Date1)>@Date2
Set @Diff= @Diff-1
END
ELSE 
IF @DatePart In ('Millisecond', 'ms') 
BEGIN
Select @Diff = DateDiff(Millisecond, @Date1, @Date2)
IF DateAdd(Millisecond, @Diff, @Date1)>@Date2
Set @Diff= @Diff-1
END
IF @D1>@D2
Set @Diff= -1*@Diff
Return @Diff
END

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating