Home > Uncategorized > Ever need to use a time dimension within SQL server?

Ever need to use a time dimension within SQL server?

I wrote this code to determine if a given time / date fell within the scope of a set period. This was used in a scheduling system to determine availability of a desired appointment time.  After searching the net I was unable to find pre-constructed code. Being that the time / date format in SQL server can be a real pain to work with I thought I would publish my work for the benefit of others.


The following code will provide an accurate time dimension.


Script: Creates and Populates an Hour Dimension Table

Note: This script does hours and minutes. If seconds are needed,
then modify the datepart on the last line that increments the date.
Obviously the table will be bigger when you include seconds.

Author: Jeffery Williams
Date: 11/11/2007

You May need to use a dimention to calculate if a scheudule rides into a


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dimHour]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[dimHour]

    HourID       INT  IDENTITY (1, 1)   NOT NULL    PRIMARY KEY
,   TheDate      DATETIME   NOT NULL
,   TheTime      Datetime   NOT NULL
,   MilitaryHour INT NOT NULL
,   StandardHour  INT NOT NULL
,   TheMinute    INT NOT NULL
,   TheSecond    INT NOT NULL
,   Standard      varchar(2) NOT NULL

DECLARE    @startdate  DATETIME
DECLARE    @enddate    DATETIME
DECLARE    @date       DATETIME

SET        @startdate  =    ‘1/1/2005 12:00:00 AM’  
SET        @enddate    =    ‘1/1/2005 23:59:59 PM’ 
SET        @date       =     @startdate

WHILE    @date <= @enddate
    INSERT INTO    dimHour (TheDate, TheTime, MilitaryHour, StandardHour, TheMinute, TheSecond, Standard)
    VALUES (
         @date                                                             –TheDate
    ,    convert(nvarchar(11), @date, 114)                                 –Time format  
    ,    DATEPART(hh, @date)                                               –Military Hour
    , CONVERT(varchar(2),
               WHEN DATEPART([hour], @Date) > 12 THEN CONVERT(varchar(2), (DATEPART([hour], @Date) – 12))
               WHEN DATEPART([hour], @Date) = 0 THEN ’12’
               ELSE CONVERT(varchar(2), DATEPART([hour], @Date))
          END)                                   — Standard Hour
    ,    DATEPART(mi, @date)                                               –Minute
    ,    DATEPART(ss, @date)                                               –Second
    , CASE WHEN DATEPART(hh, @date) between 0 and 11 THEN ‘AM’ ELSE ‘PM’ END  

    SET  @date  =    DATEADD(mi, 1, @date) — ** Modify the datepart to ss if seconds are needed


Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: