Compare Two Dates

I need to compare tow days and number of hours between two days. Then I need to deduct non-business days and hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm) from this and find net hours. How can I do this?

Hi, the following code may help you...This function returns the number of Business Days then you can multiply 12 to get total business hours...

Create Function dbo.TotalBusinessDays(@.StartDate as DateTime,@.EndDate as DateTime)
Returns Integer as
Begin
Declare @.TempDate as DateTime
Declare @.TotalDays as Int
Declare @.StartDay as Int
Declare @.EndDay as Int
Declare @.TotalWeeks as Int
Declare @.DaysExclude as Int
Declare @.T as int
Declare @.Yend as Int
Declare @.Ystart as Int
Declare @.I as int


Select @.TotalDays = DateDiff(DD,@.StartDate,@.EndDate)
If @.TotalDays<0
Begin
Select @.TempDate=@.EndDate,@.EndDate=@.StartDate,@.StartDate=@.TempDate,@.TotalDays=Abs(@.TotalDays)
End

If DatePart(YY,@.EndDate) = DatePart(YY,@.StartDate)
Begin
Select @.TotalDays=@.TotalDays+1,@.TotalWeeks = Datepart(wk,@.EndDate) - Datepart(wk,@.StartDate)
End

If DatePart(YY,@.EndDate) <> DatePart(YY,@.StartDate)
Begin
Select @.TotalDays=@.TotalDays+1 , @.Yend = DatePart(YY,@.EndDate),@.Ystart = DatePart(YY,@.StartDate) , @.I=1
Select @.TotalWeeks = 53 * (@.Yend-@.Ystart) + DatePart(Wk,@.EndDate)
While @.i <= @.Yend - @.Ystart
Begin
if DatePart(yy,dbo.DateOfWeekDay('SUN',Convert(Varchar,@.Ystart + @.i) + '-01-01')) = DatePart(YY,Convert(Varchar,@.Ystart + @.i -1) + '-12-31')
Begin
Select @.TotalWeeks = @.TotalWeeks -1
End
Select @.i= @.i +1
End
Select @.TotalWeeks = @.TotalWeeks - Datepart(wk,@.StartDate)
End
Select @.StartDay = datepart(w,@.StartDate), @.EndDay = datepart(w,@.EndDate)
If @.TotalWeeks = 0
Begin
Select @.DaysExclude = Case When @.EndDay=1 or @.EndDay=7 Then 1 Else 0 End + Case When @.StartDay=1 Or @.StartDay=7 Then 1 Else 0 End
End

If @.TotalWeeks <> 0
Begin
Select @.DaysExclude = @.TotalWeeks*2 + Case When (@.EndDay=7) Then 1 Else 0 End + Case When @.StartDay=1 Then 1 Else 0 End
End
Select @.TotalDays = @.TotalDays - @.DaysExclude
Select @.TotalDays = Case When @.TotalDays <0 then 0 Else @.TotalDays End
Return @.TotalDays
End

go

CREATE Function dbo.DateOfWeekDay(@.day as varchar(3),@.CurrDate as DateTime)
returns datetime
as
Begin
Declare @.I as int
Declare @.SunDayDate as DateTime

Select @.I = Case Upper(@.day)
When 'SUN' Then 0
When 'MON' Then 1
When 'TUE' Then 2
When 'WED' Then 3
When 'THR' then 4
When 'FRI' then 5
When 'SAT' then 6
End

Select @.SunDayDate=Convert(varchar,DateAdd(d,-(DatePart(W,@.CurrDate)-1), @.CurrDate),101)
Return DateAdd(d,@.I,@.SunDayDate)
End

go

Select dbo.TotalBusinessDays('2006-01-01','2006-01-10') * 12

--Result : 120 Hrs

|||

Thank you. This is great help.

How can I get this work for a specific Holidays too. I can specify Holiday and system should exclude them.

|||Following might help you

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&whichpage=1

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

http://www.databasejournal.com/features/mssql/article.php/3502256

http://codeinet.blogspot.com/2006/08/auxiliary-calendar-table-for-sql.html

http://www.tek-tips.com/faqs.cfm?fid=2968

Gurpreet S. Gill|||

You would be best served by using a Calendar table. By using a Calendar table, you can control for workdays, holidays, special days, odd reporting periods, etc.

See:

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

Datetime -How to count the number of business days
http://www.aspfaq.com/show.asp?id=2453