Very simplistic perhaps, but something that keeps reoccuring for me in reporting needs: calculating the times a certain day comes back in a date range, so to find it back posted it here ;-)
vb.net :
Function NumberOfDays(ByVal Day As DayOfWeek, ByVal DateFrom As DateTime, ByVal DateTo As DateTime) As Integer
Dim N As Integer
N = Math.Ceiling((DateTo.Subtract(DateFrom).Days + DateFrom.DayOfWeek + 6 - DateTo.DayOfWeek) / 7)
If Day < DateFrom.DayOfWeek Then N -= 1
If Day > DateTo.DayOfWeek Then N -= 1
Return N
End Function
T-sql :
create function UDF_NumberOfDays
(@DayOfWeek int,@from datetime,@to datetime)
returns int
as
begin
declare @N int,@dwf int, @dwt int
set @dwf = datepart(dw,@from)
set @dwt = datepart(dw,@to)
set @N = Ceiling((datediff(day,@from,@to) + @dwf + 6 - @dwt) / cast(7 as float)) --the cast is necessary to make the result a float
if @dayofweek < @dwf set @n = @n - 1
if @dayofweek > @dwt set @n = @n - 1
return @n
end