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

Read 652 times
(4 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating