Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Unanswered: 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 ...

    Code:
    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 ...





    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    I am planning to have a holidaze table ..... just started on this API about two hours back ... jump in with ideas ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Just to bring this back to the first page ... bump !!!
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •