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 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.
This is Jeremiah
I live in Portland, OR. I have two dogs.
I recently received a Master's of Science in Computer Science from Portland State University.
I'm was Microsoft MVP from 2009 - 2018 with a pile of certifications. Somewhere along the way, I wrote a database client for Riak and then handed it off to the community.