Compare Two Dates

I need to compare tow dates DateField1 and DateField2 and find number of hours between these two dates. 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 jim

could you give with examples what you want to do you question is not very clear?

thanks,

satish.

|||

Ok. Let's say I have DateField1=12/15/2006 7:00pm and DateField2=12/18/2006 9:00am

Then the difference should be 2 hours because between 12/15/2006 7:00pm and 12/18/2006 7:00am is not a business period, the rest is 2 business hours.

|||

Hi

What you need is not as simple as it appears.

Here is some code sample but you still need to add more code to finish it:

declare @.resulthourint declare @.resultdayint declare @.begin_end_hourintdeclare @.fromDatedatetimedeclare @.thruDatedatetimeset @.fromDate ='12/15/2006 6:00am'set @.thruDate ='12/18/2006 9:00am'set @.begin_end_hour = 0--caculate the first work hourif(datepart(hh,@.fromDate) <datepart(hh,'7:00am'))beginset @.begin_end_hour = @.begin_end_hour +datepart(hh,'7:00pm') -datepart(hh,'7:00am')endelseif(datepart(hh,@.fromDate) >datepart(hh,'7:00am')anddatepart(hh,@.fromDate) <datepart(hh,'7:00pm'))beginset @.begin_end_hour = @.begin_end_hour +datepart(hh,'7:00pm') -datepart(hh,@.fromDate)endprint @.begin_end_hour--todo caculate the end day work hour-- add code here--todo remember to check @.fromDate and @.thruDate are located in the same day--todo remember to reset the @.fromDate to next day and @.thruDate to the day beforeset @.fromDate =dateadd(day,casewhendatepart(weekday, @.fromDate) % 7 <= 1then 2 -datepart(weekday, @.fromDate) % 7else 0end, @.fromDate)print @.fromDateset @.thruDate =dateadd(day,casewhendatepart(weekday, @.thruDate) % 7 <= 1then -1 -datepart(weekday, @.thruDate) % 7else 0end, @.thruDate)print @.thruDateset @.resultday =datediff(hour,@.fromDate,@.thruDate) / 24 -datediff(week,@.fromDate,@.thruDate) * 2if(@.resultday < 0)set @.resultday = 0print @.resultdayset @.resulthour = @.resultday * (datepart(hh,'7:00pm') -datepart(hh,'7:00am')) + @.begin_end_hourprint @.resulthour

If you have any other problems pls let us know.

Hope this helps.

|||

Hi,

DateTime.ParseExact() supports parsing string to DateTime.

DateTime dt = DateTime.ParseExact("03/29/06", "MM/dd/yy", frmt);

Then you can use DateTime.Compare() to compare your DataTime instances:

DateTime t1( 100 );DateTime t2( 20 );if ( DateTime::Compare( t1, t2 ) > 0 ) Console::WriteLine( "t1 > t2" );if ( DateTime::Compare( t1, t2 ) == 0 ) Console::WriteLine( "t1 == t2" );if ( DateTime::Compare( t1, t2 ) < 0 ) Console::WriteLine( "t1 < t2" );
 
For more information, please see
http://msdn2.microsoft.com/en-us/library/system.datetime.parseexact(VS.80).aspx
http://msdn2.microsoft.com/en-us/library/system.datetime.compare(VS.80).aspx

|||

Hi guy's i'm stuck in a similar problem.

The problem is that i have two fields one shows theLogIn timeof the user and other shows theDuration since the user LogIn

and now i want to store the logout time which is addition ofLogIn time andDuration How i achive this.Plz Help me.

thanx In advance.

Mangat Phogat

Alea IT Soluations

Jaipur(India)

|||

This is what you want i think.

You have few informations fixed .

like Monday-Friday are your working days with timing 7:00 AM to 7PM

that makes (12 hours on 5 days) which equals 60 hours.

Total days are 7 so 7*12 = 84

Your non working hours will be 84-60= 24 hours .

Number of hours will be 7*12 =84;

if your days in between are non working days ...like sat and sunday deduct 24 from the figure u get.

A simple pseudocode might help you

DateTime firstDate = Convert.ToDateTime("8/24/2007");
DateTime secondDate = Convert.ToDateTime("8/31/2007");

DifferenceofDays = firstDate.Day-secondDate.Day // this will give 7

DateTime tempDate = date1;
int nonWorkingHours = 0;

//Check whether there is a non working day in between then add the corresponding non working hours.

for (int dayIndex = 1; dayIndex <= dayDifference; dayIndex++)
{
tempDate = tempDate.Date.AddDays(1);
if (tempDate.Date.DayOfWeek == DayOfWeek.Saturday
|| tempDate.Date.DayOfWeek == DayOfWeek.Sunday)
{
nonWorkingHours += 12;
}
}

int totalWorkingHours = dayDifference * 12;
int netHours = totalWorkingHours - nonWorkingHours;

Thanks

Muhammad Tabish Sarwar

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