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 ...You forgot holidaze, daylight savings time (which is different all over the world)...what else...

I think you're still going to needs a "schedule" table...|||I am planning to have a holidaze table .... just started on this API about two hours back ... jump in with ideas ...|||Just to bring this back to the first page ... bump !!!