date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

date

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-09-30 : 09:58:13

Hi,
How do I use the datediff function to get the difference of two dates in days or hours but excluding weekends?
I would only like to consider hours of 9-5 Mon-Fri
Thanks

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-09-30 : 11:09:23

DATEPART - Deterministic except when used as DATEPART (dw, date). dw, the weekday datepart, depends on the value set by SET DATEFIRST, which sets the first day of the week.

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-30 : 11:11:45

**********CROSS POST ************

Where's the Warrior Princess Police?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40692

Brett

8-)

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-09-30 : 11:52:30

I don't know a formula for that but you can create a function or SP with two parameters (dates)

--using a table
Declare @tblDate table (myDate datetime)
Declare @date1 datetime, @date2 datetime

set nocount on

set @date1='9/9/2004'
set @date2='9/16/2004'

while @date1<@date2
begin
    if datepart(dw,@date1)<6
        Insert @tblDate Values (@date1)
    set @date1=@date1 +1        
end

select count(*) from @tblDate
Go

--using a variable counter
Declare @dayCounter tinyint
Declare @date1 datetime, @date2 datetime

set @date1='9/9/2004'
set @date2='9/16/2004'
set @dayCounter = 0

while @date1<@date2
begin
    if datepart(dw,@date1)<6
        set @dayCounter = @dayCounter + 1
    set @date1 = @date1 +1        
end

Print @dayCounter

--return

If you want to exclude holidays I guess you have to create a table with the holidays and add that constraint to the code

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-09-30 : 13:24:05

Try this formula for working days (1-5) in days
The difference in Weeks times the working days in a weeks minus the
difference between the day of the week of the first date and the second one


datediff(week,@date1,@date2) * 5 - 
    datepart(dw,@date1) + 
    datepart(dw,@date2)

I did some tests and I think is correct


--test
print datediff(week,'11/5/2004','11/11/2004') * 5 - 
    datepart(dw,'11/5/2004') + 
    datepart(dw,'11/11/2004')
print datediff(week,'11/2/2004','11/10/2004') * 5 -  
    datepart(dw,'11/2/2004') + 
    datepart(dw,'11/10/2004')
print datediff(week,'11/4/2004','11/16/2004') * 5 -  
    datepart(dw,'11/4/2004') + 
    datepart(dw,'11/16/2004')
print datediff(week,'11/1/2004','11/18/2004') * 5 -  
    datepart(dw,'11/1/2004') + 
    datepart(dw,'11/18/2004')
print datediff(week,'11/5/2004','11/8/2004') * 5 -  
    datepart(dw,'11/5/2004') + 
    datepart(dw,'11/8/2004')

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-09-30 : 15:19:15

See same formula considering values from a holiday table
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40692

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources