# SQL datediff that excludes weekends



## DataBaseDev (Mar 31, 2006)

Hi All,

I am writing a stored proc in SQL 2000 that delivers an output to my Business Objects business view.
The idea is to measure the amount of minutes between a support case being opened (in HEAT) and the time it was closed. I have produced the SQL that does this but I would like to be able to exlude Saturdays and Sundays because the clock isn't ticking at the weekend.
Does anyone know of a way to measure a datediff period of working days only?

DataBaseDevil


----------



## cristobal03 (Aug 5, 2005)

Hmm. I'm familiar with Access and Jet but not SQL Server. Does SQL Server support scalar functions like *Date()* and *Weekday()*?

chris.


----------



## DataBaseDev (Mar 31, 2006)

Hi Chris,

Yes, there's getdate() and datepart, which will bring out the month, day etc. There's also the weekday function which gives you 1 - 7 on a date. What I was hoping was that I wouldn't have to get into loops that add up NON 6 and 7 days in a period, but I think thats where its going to go. Still it's Friday so I guess I'll switch off and sleep on it. Thanks for the reply.

Tony


----------



## cristobal03 (Aug 5, 2005)

Yeah, that's what I was going to suggest. As far as I know, it's the only way to accomplish the requirement. But then again, I don't know SQL Server; there may be an easier way.

Anyway, if you are able to get this solved, could you post back the resolution method for future readers, and mark this "Solved" using the *Thread Tools* at the top of the page? And of course, if you have any more questions, post away; if we can help, we'll do our best.

Thanks.

chris.


----------

