Calendar Table? Yes please!

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


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