Business Hours

Does anyone know how to program a "business hours" datetime subtraction? I
have a table with business hours and want to determine the # of business
hours from now to a starting date. Below is what I am using now, but need
to only add in business hours between WorkDayStarts and WorkDayEnds.
SELECT dbo.RepairOrder.RepairOrderID, DATEDIFF([Hour],
dbo.RepairOrder.EstimateLockDate, GETDATE()) AS BusHrs,
dbo.BodyShop.WorkdayStarts, dbo.BodyShop.WorkdayEnds,
dbo.RepairOrder.EstimateLockDate
FROM dbo.BodyShop CROSS JOIN
dbo.RepairOrder
WHERE (dbo.RepairOrder.EstimateLockDate IS NOT NULL)
Thanks.
DavidMight want to read this too:
http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:Of$uHcj6FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Does anyone know how to program a "business hours" datetime subtraction?
> I have a table with business hours and want to determine the # of business
> hours from now to a starting date. Below is what I am using now, but need
> to only add in business hours between WorkDayStarts and WorkDayEnds.
> SELECT dbo.RepairOrder.RepairOrderID, DATEDIFF([Hour],
> dbo.RepairOrder.EstimateLockDate, GETDATE()) AS BusHrs,
> dbo.BodyShop.WorkdayStarts, dbo.BodyShop.WorkdayEnds,
> dbo.RepairOrder.EstimateLockDate
> FROM dbo.BodyShop CROSS JOIN
> dbo.RepairOrder
> WHERE (dbo.RepairOrder.EstimateLockDate IS NOT NULL)
>
> Thanks.
> David
>