select DATEADD(DD,number,'01-01-2010') from dbo.spt_values where type='P'
and DATENAME(dw, DATEADD(DD,number,'01-01-2010') )not in ('Saturday','Sunday')

quote:

---

Originally posted by Maverick_

I am trying to figure out how the db is going to realise what counts as a "weekend" and the thing is, the database I am working with - I cannot create tables in it where I could enter e.g. 2010 weekend dates. Hm

---

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME ='BusinessHours' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE='FUNCTION')
DROP FUNCTION dbo.BusinessHours
GO
CREATE FUNCTION dbo.BusinessHours
(
@StartDate datetime,
@EndDate datetime
)
RETURNS  int
AS
BEGIN
DECLARE @TotalHours int
;With Calendar_CTE (Date,Day,WeekDay)
AS
(
SELECT DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),DATENAME(dw,DATEADD(dd,DATEDIFF(dd,0,@StartDate),1)), CASE WHEN DATENAME(dw,DATEADD(dd,DATEDIFF(dd,0,@StartDate),1)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END

UNION ALL

SELECT DATEADD(dd,1,Date),DATENAME(dw,DATEADD(dd,1,Date)), CASE WHEN DATENAME(dw,DATEADD(dd,1,Date)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END
FROM Calendar_CTE
WHERE DATEADD(dd,1,Date) <=  DATEADD(dd,DATEDIFF(dd,0,@EndDate),0)
)


SELECT @TotalHours=SUM(
						CASE	WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@StartDate),0) THEN DATEDIFF(MINUTE,@StartDate,DATEADD(dd,DATEDIFF(dd,0,@StartDate),1)) 
								WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,DATEADD(dd,DATEDIFF(dd,0,@EndDate),0),@EndDate)
								ELSE 24*60
						END
					   )/60
FROM Calendar_CTE c
LEFT JOIN Holiday h
ON h.[date]= c.Date
WHERE WeekDay=1
AND h.[date] IS NULL
OPTION (MAXRECURSION 0)

RETURN @TotalHours
END


then use it like below

SELECT e.enquiry_number, e.enquiry_desc, e.enquiry_time
FROM enquiry e
INNER JOIN status s
ON s.enquiry_number=e.enquiry_number
WHERE dbo.BusinessHours(s.logged_date ,GETDATE()) < = 24