Call To Gurus -- SQLDateTimeAPI

Hey gurus ...

I am in the process of writing a DateTime API for SQL Server 2000 ...
Here's what I have come up with so far ...


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_FirstDayOfMonth]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_FirstDayOfMonth]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_LastDayOfMonth]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_LastDayOfMonth]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_LastWorkingDayOfMonth]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_LastWorkingDayOfMonth]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_WorkingDaysBetweenDates]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_WorkingDaysBetweenDates]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_PopulateTimeSeed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_PopulateTimeSeed]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TimeSeed]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TimeSeed]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE Function
fn_FirstDayOfMonth (@SearchDate datetime)
Returns
Datetime
as
Begin
Declare @ReturnDate datetime

Select
@ReturnDate = CalendarDate
from
TimeSeed
where
CalendarYear = Year(@SearchDate)
and CalendarMonth = Month(@SearchDate)
and DateOfMonth = 1

Return @ReturnDate
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Create Function
fn_LastDayOfMonth (@SearchDate datetime)
Returns
Datetime
as
Begin
Declare @ReturnDate datetime

Select
@ReturnDate = Max(CalendarDate)
from
TimeSeed
where
CalendarYear = Year(@SearchDate)
and CalendarMonth = Month(@SearchDate)
Return @ReturnDate
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Create Function
fn_LastWorkingDayOfMonth (@SearchDate datetime)
Returns
Datetime
as
Begin
Declare @ReturnDate datetime

Select
@ReturnDate = Max(CalendarDate)
from
TimeSeed
where
CalendarYear = Year(@SearchDate)
and CalendarMonth = Month(@SearchDate)
and WorkingDayFlag = 1
Return @ReturnDate
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE Function
fn_WorkingDaysBetweenDates (@SearchDate1 datetime, @SearchDate2 datetime)
Returns
int
as
Begin
Declare @CountDate int

Select
@CountDate = Count(CalendarDate)
from
TimeSeed
where
CalendarDate >= @SearchDate1
and CalendarDate <= @SearchDate2
and WorkingDayFlag = 1
Return @CountDate
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].[TimeSeed] (
[CalendarDate] [datetime] NOT NULL ,
[DateOfMonth] [int] NOT NULL ,
[DayOfMonth] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CalendarMonth] [int] NOT NULL ,
[CalendarYear] [int] NOT NULL ,
[FiscalYear] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FiscalMonth] [int] NOT NULL ,
[WorkingDayFlag] [bit] NOT NULL ,
[WorkingHours] [int] NOT NULL ,
[WeekOfYear] [int] NOT NULL ,
[WeekOfMonth] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TimeSeed] WITH NOCHECK ADD
CONSTRAINT [PK_TimeSeed] PRIMARY KEY CLUSTERED
(
[CalendarDate]
) ON [PRIMARY]
GO
CREATE INDEX [IX_TimeSeed] ON [dbo].[TimeSeed]([DateOfMonth]) ON [PRIMARY]
GO
CREATE INDEX [IX_TimeSeed_1] ON [dbo].[TimeSeed]([CalendarYear]) ON [PRIMARY]
GO
CREATE INDEX [IX_TimeSeed_2] ON [dbo].[TimeSeed]([CalendarMonth]) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create Procedure usp_PopulateTimeSeed
as
Declare
@StartDate datetime
Select
@StartDate = '19000101'
Set DateFirst 1
While @StartDate <= '21001231'
begin
insert into TimeSeed
(
IntDate
,CalendarDate
,DateOfMonth
,DayOfMonth
,CalendarMonth
,CalendarYear
,FiscalYear
,FiscalMonth
,WorkingDayFlag
,WorkingHours
,WeekOfYear
,WeekOfMonth
)
Select
Cast (@StartDate as int)
,@StartDate
,Day(@StartDate)
,Datepart(DW,@StartDate)
,Month(@StartDate)
,Year(@StartDate)
,Case
When Month(@StartDate) <= 3
Then Year(@StartDate) - 1
Else Year(@StartDate)
End
,Case
When Month(@StartDate) <= 3
Then Month(@StartDate) + 9
Else Month(@StartDate) - 3
End
,Case
When Datepart(DW,@StartDate) in (1,7)
Then 0
else 1
End
,8
,Datepart(wk,@StartDate)
,(Day(@StartDate)%7)+1

Select
@StartDate = Dateadd(dd,1,@Startdate)
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


Here's what has me stumped

I'd like to calculate the hours difference for a given start and end datetime in SQL server. Certainly function datediff() can do the job but the tricky part is that the hours calculation should be based on 8 hrs / per business day (from 8:00am to 5:00pm), and weekends (Saturdays and Sundays) are excluded as well.

For example:
start time: 11/10/04 4:00pm
end time: 11/11/04 8:00 am
then the hours difference should be only 1 hour. (if there is fraction, then round to nearest hour).


Now ... remember that the table TimeFeed is modifiable and can be changed to suit our needs.

Any ideas ???

Also ... you can all chip in with your own functions ...