Calculating the Number of Business Hours Passed Since a Point of Tme

SQLServerCentral is supported by Red Gate Software Ltd.

Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mazharuddin Ehsan » Calculating the Number of Business Hours...

35 posts, Page 4 of 4

«««1 2 3 4

Calculating the Number of Business Hours Passed Since a Point of Tme

Rate Topic

Display Mode

Topic Options

Author

Message

Jeff Moden

Posted Thursday, September 24, 2009 5:41 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 20,023, Visits: 13,596

Thanks for the feedback, Mazharuddin... I'll take a look.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #793624

Jeff Moden

Posted Thursday, September 24, 2009 5:51 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 20,023, Visits: 13,596

I ran your code from the previous post... here it is, again...

select getdate()
select dbo.CalcTimeBetweenTwoDates('2004-11-16 15:30', '2007-11-21 07:31')
select getdate()

Here's what I get for an error message from that code...

Msg 4121, Level 16, State 1, Line 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.CalcTimeBetweenTwoDates", or the name is ambiguous.

Looking at the code from your good article, I see no function called dbo.CalcTimeBetweenTwoDates. I'm still looking, though.

--Jeff Moden

Post #793626

Jeff Moden

Posted Thursday, September 24, 2009 5:56 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 20,023, Visits: 13,596

I get it... you're not running the same code as in the article. Please post the code you are running. Thanks.

--Jeff Moden

Post #793627

Mazharuddin Ehsan

Posted Friday, September 25, 2009 3:50 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 7:40 AM
Points: 357, Visits: 1,960

Jeff Moden (9/24/2009)

I get it... you're not running the same code as in the article. Please post the code you are running. Thanks.

You are right Jeff. The code in the article Calculating the Number of Business Hours Passed since a Point of Time does not contain the code for the function CalcTimeBetweenTwoDates.
I posted it later in the discussion forum of the article as a reader requested for it. Please find attached the code for the function CalcTimeBetweenTwoDates in the discussion forum of the article

Mazharuddin Ehsan (11/28/2007)

Comments posted to this topic are about the item Calculating The Number Of Business Hours Passed Since a Point of Time

The comments of some of the readers motivated me to do enhancements in the solution:
1. To calculate duration between two values of time.
2. Considering the holidays.

See the attachment for the details.
Edited: 1/13/2008 5:43 PM by Mazharuddin Ehsan

CalculatingDurationOfOfficeHoursPassedBetweenTwoPointsOfTime.doc

-Mazharuddin

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time

Post #793733

geoff.walton

Posted Monday, November 30, 2009 9:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 15, 2010 6:24 AM
Points: 1, Visits: 15

Hey Guys,

Thanks for the really great functions, but I'm having some wierd issue here. I know this is a old thread, sorry.

We have a shift cycle from 7am - 10 pm here with a 1 hour lunch. I have altered the lunch calc to 3600 which works fine and changed the dateFN's to hours that we use. So basically there are 14 working hours in days (long days).

The problem is that when ever I try calculate hours over a date period greater than 2 days, I loose 6 hours per day?

I am more than confused...

select dbo.CalcTimeBetweenTwoDates('2009-11-16 06:00', '2009-11-16 22:30')
14 --(as expected)
select dbo.CalcTimeBetweenTwoDates('2009-11-16 06:00', '2009-11-17 22:30')
28 --(as expected)
select dbo.CalcTimeBetweenTwoDates('2009-11-16 06:00', '2009-11-18 22:30')
36 -- (6 hours short????)
select dbo.CalcTimeBetweenTwoDates('2009-11-16 06:00', '2009-11-19 22:30')
44 --(12 hours short now???)

Post Attachments

Functions.doc (5 views, 52.50 KB)

Post #826287

« Prev Topic | Next Topic »

35 posts, Page 4 of 4

«««1 2 3 4

Permissions