Build DATELOOKUP table
This script creates a table named DATELOOKUP which is used for looking up date related values. Each record represents date related info about the date in the column dtDate. For example the iMonth column stores the integer value of the month of dtDate and sShortMonth stores the abbreviated name of that same month. Use this table in a join to get any kind of date related info about a specific date that would normally require the use of an SQL function.
The script includes syntax for populating the table. The script also adds extended properties to each column that includes details on what that columns valule is.
/*
Author: Ed Carden
The following Script creates the DATELOOKUP table. And fills the table with dates between the value of @DateBegin thru @EndDate.
The DATELOOKUP table can be used as a Lookup for dates for a variety of info about dates such as last day of the month, last week,
the integer value of the month the day and the year individualy as well the string versions of the Month both in full and breif.
Basically this table is good for pulling in various date data that would typically require some type of function in your query. By
joining this table using the date you want to use as the comparison value you can get a variety of date related info without having
to use any specail functions.
*/
/****** Object: Table [DATELOOKUP] ******/if exists (select * from dbo.sysobjects where id = object_id(N'[DATELOOKUP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [DATELOOKUP]
GO
/****** Object: Table [DATELOOKUP] ******/CREATE TABLE [DATELOOKUP] (
[dtDate] [smalldatetime] NOT NULL ,
[dtPostDate] [smalldatetime] NOT NULL ,
[dtLastDate] [smalldatetime] NOT NULL ,
[iMonth] [tinyint] NOT NULL ,
[iDayOfYear] [smallint] NOT NULL ,
[iDay] [tinyint] NOT NULL ,
[iWeekDay] [tinyint] NOT NULL ,
[iLastDay] [tinyint] NOT NULL ,
[iWeek] [tinyint] NOT NULL ,
[iQuarter] [tinyint] NOT NULL ,
[iYear] [smallint] NOT NULL ,
[sMonth] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[sShortMonth] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[sWeekDay] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[sDayOfMonth] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[sLastDay] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[sWeek] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[sQuarter] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[sYear] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [DATELOOKUP] WITH NOCHECK ADD
CONSTRAINT [PK_DATELOOKUP] PRIMARY KEY CLUSTERED
(
[dtDate]
) ON [PRIMARY]
GO
exec sp_addextendedproperty N'MS_Description', N'Date including Month, day & Year', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'dtDate'
GO
exec sp_addextendedproperty N'MS_Description', N'Post date is the first day of the month for the value of dtDate', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'dtPostDate'
GO
exec sp_addextendedproperty N'MS_Description', N'Last date is the last day of the month for the value of dtDate', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'dtLastDate'
GO
exec sp_addextendedproperty N'MS_Description', N'Numeric value of month for dtDate. January = 1, December =12', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'iMonth'
GO
exec sp_addextendedproperty N'MS_Description', N'Numeric value of day in the year of dtDate', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'iDayOfYear'
GO
exec sp_addextendedproperty N'MS_Description', N'Numeric value of day in the month for dtDate', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'iDay'
GO
exec sp_addextendedproperty N'MS_Description', N'Integer value of day of the week', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'iWeekDay'
GO
exec sp_addextendedproperty N'MS_Description', N'Numeric value of last day in the month of dtDate', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'iLastDay'
GO
exec sp_addextendedproperty N'MS_Description', N'Numerical value of week in the year of dtDate', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'iWeek'
GO
exec sp_addextendedproperty N'MS_Description', N'Numerical quarter of year of dtDate', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'iQuarter'
GO
exec sp_addextendedproperty N'MS_Description', N'Numeric value of year for dtDate.', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'iYear'
GO
exec sp_addextendedproperty N'MS_Description', N'Complete name of month.', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'sMonth'
GO
exec sp_addextendedproperty N'MS_Description', N'Abreviated or short name of Month. January = Jan', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'sShortMonth'
GO
exec sp_addextendedproperty N'MS_Description', N'Day Of Week Name.', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'sWeekDay'
GO
exec sp_addextendedproperty N'MS_Description', N'Text equivelant of day of month value (iDay)', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'sDayOfMonth'
GO
exec sp_addextendedproperty N'MS_Description', N'Text equivelant of last day of month value (iLastDay)', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'sLastDay'
GO
exec sp_addextendedproperty N'MS_Description', N'Text equivelant of Week in the year (iWeek)value.', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'sWeek'
GO
exec sp_addextendedproperty N'MS_Description', N'Text equivelant of iQuarter', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'sQuarter'
GO
exec sp_addextendedproperty N'MS_Description', N'Text equivelant of Year (iYear) value.', N'user', N'dbo', N'table', N'DATELOOKUP', N'column', N'sYear'
Go
/*This section populates the DATELOOKUP Table
*/Declare @DateBegin SmallDateTime
Declare @DateEnd SmallDateTime
Set @DateBegin = '2000-01-01'
Set @DateEnd = '2005-12-31'
DELETE FROM DATELOOKUP WHERE dtDate Between @DateBegin And @DateEnd
while @DateBegin <= @DateEnd
begin
INSERT INTO DATELOOKUP
SELECT @DateBegin As 'dtDate',
DateAdd(d,-DatePart(dd,@DateBegin)+1,@DateBegin) AS 'dtPostDate',
DateAdd(d,-DatePart(dd,DateAdd(m,1,@DateBegin)),DateAdd(m,1,@DateBegin)) AS 'dtLastDate',
DatePart(M,@DateBegin) AS 'iMonth',
DatePart(dy,@DateBegin) AS 'iDayOfYear',
DatePart(dd,@DateBegin) AS 'iDay',
DatePart(dw,@DateBegin) As 'iWeekDay',
DatePart(d,DateAdd(m,1,DateAdd(d,-DatePart(dd,@DateBegin),@DateBegin))) AS 'sLastDay',
DatePart(wk,@DateBegin) AS 'iWeek',
DatePart(q,@DateBegin) AS 'iQuarter',
DatePart(yyyy,@DateBegin) AS 'iYear',
DateName(mm,@DateBegin) AS 'sMonth',
Case DatePart(M,@DateBegin)
When 1 Then 'Jan' When 2 Then 'Feb' When 3 Then 'Mar' When 4 Then 'Apr'
When 5 Then 'May' When 6 Then 'Jun' When 7 Then 'Jul' When 8 Then 'Aug'
When 9 Then 'Sep' When 10 Then 'Oct' When 11 Then 'Nov' When 12 Then 'Dec'
Else '' End As 'sShortMonth',
DateName(dw,@DateBegin) As 'sWeekDay',
DateName(d,@DateBegin) AS 'sDayOfMonth',
Convert(VarChar(2),DatePart(d,DateAdd(m,1,DateAdd(d,-DatePart(dd,@DateBegin),@DateBegin)))) AS 'sLastDay',
DateName(wk,@DateBegin) AS 'sWeek',
DateName(q,@DateBegin) AS 'sQuarter',
DateName(yyyy,@DateBegin) AS 'sYear'
Set @DateBegin = DateAdd(dd,1,@DateBegin)
end 
