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

Compare data in tow users

Does anybody have a script that would be able to compare two users (oracle)
or dB (MSSQL) tables by table, assuming that schema is identical?
Thx
YuriWYou want a tool like SQL Data compare:
http://www.red-gate.com/sql/summary.htm
-- Keith
"Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message =news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> Does anybody have a script that would be able to compare two users =(oracle)
> or dB (MSSQL) tables by table, assuming that schema is identical?
> > Thx
> > YuriW
> >|||I'd rather prefer a sql script.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uG6cjlzkDHA.2964@.tk2msftngp13.phx.gbl...
You want a tool like SQL Data compare:
http://www.red-gate.com/sql/summary.htm
--
Keith
"Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> Does anybody have a script that would be able to compare two users
(oracle)
> or dB (MSSQL) tables by table, assuming that schema is identical?
> Thx
> YuriW
>|||Check the following article:
http://www.sql-server-performance.com/vg_database_comparison_sp.asp
-Sue
On Wed, 15 Oct 2003 17:13:13 GMT, "Yuri Weinstein"
<yuriw@.hotmail.com> wrote:
>I'd rather prefer a sql script.
>"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
>news:uG6cjlzkDHA.2964@.tk2msftngp13.phx.gbl...
>You want a tool like SQL Data compare:
>http://www.red-gate.com/sql/summary.htm|||in oracle, generate a script from the data dictionary, something like this:
select 'select * from user1.'||table_name|| ' minus select * from user2.' ||
table_name || ';'
from dba_tables where owner = 'USER1';
this show any rows in USER1 that don't exist in USER2 (or have slight
differences)
you could also generate a join or a more complex comparison statement,
depending on what you're looking for
either spool out the output in SQL*Plus, or but this in a PL/SQL block and
use EXECUTE IMMEDIATE to run the generated statements
note that this will not work for tables that have LONG columns
---
Mark C. Stock
www.enquery.com
(888) 512-2048
"Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> Does anybody have a script that would be able to compare two users
(oracle)
> or dB (MSSQL) tables by table, assuming that schema is identical?
> Thx
> YuriW
>|||SQL queries are not a terribly good tool for doing this type of comprison.
It would be easier to simply dump out the tables (BCP for MS SQL Server)
into two directories, one for each database. Then, you can compare the
tables -- i.e. their exported files -- using one of many file comparison
tools.
Also, there are commercial tools such as SQL Compare from Red Gate.
I do this myself with Perl scripts. If you are interested, email me and I'll
drop you a copy.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"mcstock" <mcstock@.enquery.com> wrote in message
news:KK6dnf9cybED0gWiRVn-jA@.comcast.com...
> in oracle, generate a script from the data dictionary, something like
this:
> select 'select * from user1.'||table_name|| ' minus select * from user2.'
||
> table_name || ';'
> from dba_tables where owner = 'USER1';
> this show any rows in USER1 that don't exist in USER2 (or have slight
> differences)
> you could also generate a join or a more complex comparison statement,
> depending on what you're looking for
> either spool out the output in SQL*Plus, or but this in a PL/SQL block and
> use EXECUTE IMMEDIATE to run the generated statements
> note that this will not work for tables that have LONG columns
> ---
> Mark C. Stock
> www.enquery.com
> (888) 512-2048
>
> "Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
> news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> > Does anybody have a script that would be able to compare two users
> (oracle)
> > or dB (MSSQL) tables by table, assuming that schema is identical?
> >
> > Thx
> >
> > YuriW
> >
> >
>|||thx, Mark.
"mcstock" <mcstock@.enquery.com> wrote in message
news:KK6dnf9cybED0gWiRVn-jA@.comcast.com...
> in oracle, generate a script from the data dictionary, something like
this:
> select 'select * from user1.'||table_name|| ' minus select * from user2.'
||
> table_name || ';'
> from dba_tables where owner = 'USER1';
> this show any rows in USER1 that don't exist in USER2 (or have slight
> differences)
> you could also generate a join or a more complex comparison statement,
> depending on what you're looking for
> either spool out the output in SQL*Plus, or but this in a PL/SQL block and
> use EXECUTE IMMEDIATE to run the generated statements
> note that this will not work for tables that have LONG columns
> ---
> Mark C. Stock
> www.enquery.com
> (888) 512-2048
>
> "Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
> news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> > Does anybody have a script that would be able to compare two users
> (oracle)
> > or dB (MSSQL) tables by table, assuming that schema is identical?
> >
> > Thx
> >
> > YuriW
> >
> >
>
>|||Hi Mark,
I am alomst there, but just not yet. Can you help me with this sql.
Here are exact steps:
1. set pages 5555 (trying to get all garbidge from temp.sql and it does not
do it?)
2. spool temp.sql
3. select 'select count(*) from magnetic.' || table_name ||';' from
dba_tables
where owner = 'MAGNETIC' ORDER BY table_name;
This statement works.
4. spool off
5. @.temp.sql (with an exception some errors like I said in #1).
Now if I run -
select 'select count(*) from magnetic.' || table_name ||';' from dba_tables
where owner = 'MAGNETIC' ORDER BY table_name||';'||select 'select count(*)
from
magnetic.' || table_name ||';' from dba_tables where owner = 'MAGNETIC'
ORDER B
Y table_name;
I get - ORA-00936: missing expression
Where is the error in sql?
Thanks in advance.
YuriW
"mcstock" <mcstock@.enquery.com> wrote in message
news:KK6dnf9cybED0gWiRVn-jA@.comcast.com...
> in oracle, generate a script from the data dictionary, something like
this:
> select 'select * from user1.'||table_name|| ' minus select * from user2.'
||
> table_name || ';'
> from dba_tables where owner = 'USER1';
> this show any rows in USER1 that don't exist in USER2 (or have slight
> differences)
> you could also generate a join or a more complex comparison statement,
> depending on what you're looking for
> either spool out the output in SQL*Plus, or but this in a PL/SQL block and
> use EXECUTE IMMEDIATE to run the generated statements
> note that this will not work for tables that have LONG columns
> ---
> Mark C. Stock
> www.enquery.com
> (888) 512-2048
>
> "Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
> news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> > Does anybody have a script that would be able to compare two users
> (oracle)
> > or dB (MSSQL) tables by table, assuming that schema is identical?
> >
> > Thx
> >
> > YuriW
> >
> >
>
>|||"Yuri Weinstein (HotMail)" wrote...
> Now if I run -
> select 'select count(*) from magnetic.' || table_name ||';' from
dba_tables
> where owner = 'MAGNETIC' ORDER BY table_name||';'||select 'select
count(*)
---^
> from
> magnetic.' || table_name ||';' from dba_tables where owner ='MAGNETIC'
> ORDER B
> Y table_name;
> I get - ORA-00936: missing expression
> Where is the error in sql?
>
Hi Yuri,
it seems to me that you try to construct a string of 2 selects
in a select statement. But if I'm right, you have some problems
with quotes. Your statement is quite ok until you try to put
a concatenation after the ORDER BY.
I believe you want to put the from and order by clauses into
to string.
Try to work from here.
hth,
Guido