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 
