Hi Friendz,
When I was newbie in SQL, issue came in report from one project. Client want to calculate the difference between 2 dates (in hours) but excluding Saturday and Sunday.
The default DateDiff function in SQL Server is easy to use but it gives clear difference.
Syntax :
Datediff(datepart , start_date , end_date)
Way to use DateDiff function :
select DateDiff(minute, '2009-12-31 23:59:59.9999999', '2010-01-01 00:00:00.0000000')
For more details visit :
But for the above problem is not useful.
Definitely we need to create function which fulfills our requirement.
So here you go..
Create the following function in Database.
Create Function dbo.fn_GetHoursExcludeSatSun(@FromDate DateTime,@ToDate DateTime)
Returns bigint
As
Begin
Declare @ldt datetime;
Declare @dtdiff bigint;
Declare @cnt bigint;
Declare @Thrs bigint;
Declare @Thrs1 bigint;
Declare @frmhrs bigint;
Declare @tohrs bigint;
Declare @tmpFrmDate datetime;
if DateName(dw,@FromDate) ='Saturday' or DATENAME(dw,@FromDate) ='Sunday'
begin
Set @frmhrs = 0
end
else
begin
Set @frmhrs = DateDiff(hh,convert(datetime,convert(varchar(10),@FromDate,101)),@FromDate)
end
if DateName(dw,@TODate) ='Saturday' or DATENAME(dw,@TODate) ='Sunday'
begin
Set @tohrs = 0
end
else
begin
Set @tohrs = DateDiff(hh,convert(datetime,convert(varchar(10),@TODate,101)),@TODate)
end
set @Thrs=0
set @dtdiff=0
set @cnt=1
select @dtdiff=datediff(dd,convert(datetime,@FromDate),convert(datetime,@ToDate))
set @dtdiff=@dtdiff
set @ldt=convert(datetime,@FromDate)
set @tmpFrmDate=convert(datetime,@FromDate)
While (@cnt<=@dtdiff)
Begin
if DateName(dw,@ldt) ='Saturday' or DATENAME(dw,@ldt) ='Sunday'
begin
set @Thrs1=1
end
else
begin
set @Thrs = @Thrs + datediff(hh,@ldt,DATEADD(dd,@cnt,@FromDate))
end
select @ldt=DATEADD(dd,@cnt,@FromDate)
set @cnt=@cnt+1
end
set @Thrs = @Thrs - @frmhrs
set @Thrs = @Thrs + @tohrs
Return @Thrs
End
How to use this function :
select dbo.fn_GetHoursExcludeSatSun(First_Date,Second_Date)
It is possible to use the column names (with datatype 'datetime') instead of First_Date, Second_Date.
