This script counts work days (excludes weekends) ... hope it helps

declare @opendate datetime
declare @clsddate datetime


set @opendate = '02/23/06'
set @clsddate = '02/27/06'

SELECT
(DATEDIFF (DD, @OPENDATE, @CLSDDATE) + 1) -
(DATEDIFF (WW, @OPENDATE, @CLSDDATE) * 2)

-----Original Message-----
From: Steven Manross
[mailto:mssqldba-ezmlmshield-x95652925.[Email address protected]
Sent: Friday, February 24, 2006 9:57 AM
To: LazyDBA Discussion
Subject: RE: SQL server: Date difference calculation

Wouldn't you just be doing a datediff for days, and then a datediff on
weeks subtracting the datediff on weeks from the datediff on days (since
there's one Sunday in every week).

Datediff(dd,'1/1/2005','6/1/2005')-datediff(wk,'1/1/2005','6/1/2005')

The logic sounds right, but might be off by 1 day if the weeks don't
quite line up right, but this should get you started.

HTH

Steven

> -----Original Message-----
> From: adityap
> [mailto:mssqldba-ezmlmshield-x66595869.[Email address protected]
> Sent: Friday, February 24, 2006 3:28 AM
> To: LazyDBA Discussion
> Subject: SQL server: Date difference calculation
>
>
> Is there any way to calculate the interval between two times
> excluding specific days,specific duration.
>
> suppose if i need the difference between date1, date2 but i
> don't want to count how many sundays are coming, i want to
> ignore all sundays in between,i want to ignore certain
> timings(say 06.00 PM to 09:00AM)s also,then how can I get the
> duration in this way.
>
>
> Warm Regards
> Aditya Sisodia
> India Infoline Ltd.
> 022 - 56489167
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com To unsubscribe:
> http://www.lazydba.com/unsubscribe.html
>
>
>
>


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page