Calendar Table by Day

Question

Post reply

Calendar Table by Day

JustMarie

SSCertifiable

Points: 7771
More actions
February 26, 2014 at 12:05 pm

#286187

Comments posted to this topic are about the item Calendar Table by Day

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

William Soranno SSCommitted Points: 1713 More actions · Answer 1

Thank you for the post, it looks interesting.

One question.

How do you recreate it when Sql restarts?

Since tempdb is deleted and recreated on a restart.

Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143

"Quality, like Success, is a Journey, not a Destination" - William Soranno '92

JustMarie SSCertifiable Points: 7771 More actions · Answer 2

Thanks for the kind words. If I can save someone else time by sharing it's worth it.

I wrote all the calendar tables and the tally table as stored procedures. Makes them easy to recreate as long as it's in the checklist.

Manie Verster SSCertifiable Points: 7152 More actions · Answer 3

Thanks for a great article. This is exactly what I was looking for. However, I saw one flaw (if it is a flaw).

SET @End = (

SELECT DATEPART(DD, DATEADD(DD, -1, DATEADD(YY, 1, @StartDate)))

This will only give you 31 days. Looking at where you get the end of the year date DATEADD(YY, 1, @StartDate) I think you would want to populate the whole year, anyway that is what I needed so I changed it a bit so instead of the first datepart I used datediff to get 365 days.

SET @End = (

SELECT DATEDIFF(DD, DATEADD(DD, -1, DATEADD(YY, 1, @StartDate)))+1

The datediff without +1 will give you 364 days and that is why I added the +1.

This is not meant as criticism just an idea.

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

Manie Verster SSCertifiable Points: 7152 More actions · Answer 4

Sorry, I meant to add this piece. God is good! I was going to do nested while loops and then after asking Him to help, He guided me to your article!!! Wonderful!!!

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

Shakeeb.ahamed Grasshopper Points: 17 More actions · Answer 5

Thanks Bayer 🙂 for providing such a good script which serve my purpose.

I modified little get the week number and week number for the month.

DATEPART(WEEK,Day(DATEADD(DD, (N - 1), @StartDate))) as CalMonthWeek

,DATEPART(WEEK,DATEADD(DD, (N - 1), @StartDate)) as CalDayWeek

terry.home Ten Centuries Points: 1004 More actions · Answer 6

Msg 208, Level 16, State 1, Line 21

Invalid object name 'dbo.CalDay'.

SQL Server 2005 Express (with SSMS 2008)