Facility9

I finally got sick of not having a calendar table, so I went ahead and made one today. It’s nothing special and it really didn’t take much time, but I thought I would go ahead and share my script in the hopes that google will index it and somebody won’t have to think about it:

CREATE TABLE [dbo].[Calendar](
	[Date] [datetime] NOT NULL,
	[FirstDayOfMonth] [datetime] NOT NULL,
	[LastDayOfMonth] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
	[Date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO

DECLARE @the_date AS DATETIME;

SET @the_date = N'19990101'

-- Oooh, a loop! Yup, I was feeling very lazy and procedural and
-- really didn't feel like writing a nifty cross join to get 50 bajillion
-- rows or anything like that.
WHILE @the_date < (DATEADD(yyyy, 10, GETDATE()))
BEGIN
  INSERT INTO Calendar
  (
    Date,
    FirstDayOfMonth,
    LastDayOfMonth
  )
  VALUES
  (
    @the_date,
    DATEADD(mm, DATEDIFF(mm, 0, @the_date), 0),
    -- My data is not very granular by time, you might want to decrease this to
    -- three milliseconds if you have a lot of time-based data to store.
    DATEADD(ss,-1,DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@the_date),0)))
  );

  SELECT @the_date = DATEADD(dd, 1, @the_date);
END

Or, if you have a numbers table, you could do something like this for a more set based approach:

DECLARE @the_date AS DATETIME;
SET @the_date = N'19990101'

INSERT INTO Calendar
(
  Date,
  FirstDayOfMonth,
  LastDayOfMonth
)
SELECT DATEADD(dd, n, @the_date),
       DATEADD(mm, DATEDIFF(mm, 0, DATEADD(dd, n, @the_date)), 0),
       DATEADD(ss,-1,DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,DATEADD(dd, n, @the_date)),0)))
  FROM nums

My numbers table has 1,000,000 rows in it, so running this will take “some time”. You’ll probably want to add a where clause to limit the number of days you create in your calendar table if that’s the case.