SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Date Calendar


Date Calendar

Author
Message
Sean Smith (SSC)
Sean Smith (SSC)
Right there with Babe
Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)

Group: General Forum Members
Points: 739 Visits: 990
That's probably just a bad on my side. I may have originally set the columns to INT in the CREATE TABLE statement then changed it to TINYINT and forgot to adjust the corresponding ALTER TABLE statement. The NOT NULL is added after the fact because until the column is populated with data during the build, it can be NULL, but after it shouldn't be. It allows for error checking (if any values get missed during population) and allows for a developer to know that the column should always contain data when looking at the table definition.
crmitchell
crmitchell
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 1761
CELKO (7/21/2010)
4. Fixed date but slides to Monday if Saturday or Tuesday if Sunday (UK Boxing Day is the only one).


2nd Jan should also be in Scotland
Bill Wehnert
Bill Wehnert
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 67
Date tables are amazing! I can't tell you how many times I've found this simple little thing to be a huge time saver. Just being able to bang your data against a pre-determined date set can be the difference in getting a report back in a few seconds to a few hours.

My calendar table also had holidays in it (I needed those for certain charges to apply). I remember the first time I built the table and told my colleague about it, he thought I was crazy and had just wasted my time.

The first time I showed him how to easily generate a report that spanned a given week however, he changed his tune.

Thanks for this article, it included a lot of things I never included but could see being used.

Ad maiorem Dei gloriam
Sean Smith (SSC)
Sean Smith (SSC)
Right there with Babe
Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)

Group: General Forum Members
Points: 739 Visits: 990
Thanks for the kind words. Smile Funny thing is I too had a similar situation with one colleague. Changed his tune too once he saw the power behind it. Smile
Brian J. Parker
Brian J. Parker
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 298
Thanks much. I imagine most people have written some version of this with the columns they most need, but a single script collecting them all is great. I've got my own with a handful of our holidays and some other stuff.

Because this script is a one-time run and doesn't need to be efficient, those wanting is_businessday can do what I do and just add it at the end, calculating it based on is_weekday and a to-be-added holiday column... which would probably require customization for most places. Celko posted awesome resources, my own I'm-too-lazy-this-is-good-enough code for some standard U.S. holidays was was:


[Holiday] AS
CASE
WHEN MONTH(date)=1 AND DAY(date)=1 THEN 'New Years Day'
WHEN MONTH(date)=5 AND DatePart(weekday, DateAdd(day,@@DATEFIRST,[date]))=2 AND DAY(date) BETWEEN 25 AND 31 THEN 'Memorial Day'
WHEN MONTH(date)=7 AND DAY(date)=4 THEN 'Independence Day'
WHEN MONTH(date)=9 AND DatePart(weekday, DateAdd(day,@@DATEFIRST,[date]))=2 AND DAY(date) BETWEEN 1 AND 7 THEN 'Labor Day'
WHEN MONTH(date)=11 AND DatePart(weekday, DateAdd(day,@@DATEFIRST,[date]))=5 AND DAY(date) BETWEEN 22 and 28 THEN 'Thanksgiving'
WHEN MONTH(date)=12 AND DAY(date)=25 THEN 'Christmas Eve'
WHEN MONTH(date)=12 AND DAY(date)=26 THEN 'Christmas Day'
ELSE '' -- or NULL depending on your preference
END /* CASE */,


Sean Smith (SSC)
Sean Smith (SSC)
Right there with Babe
Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)

Group: General Forum Members
Points: 739 Visits: 990
Awesome! Thanks for sharing. Smile
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)

Group: General Forum Members
Points: 849 Visits: 1721
Great script! w00t

 
Sean Smith (SSC)
Sean Smith (SSC)
Right there with Babe
Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)

Group: General Forum Members
Points: 739 Visits: 990
Thank you!
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)

Group: General Forum Members
Points: 849 Visits: 1721
Sean

I haven't had a chance to review the entire script in detail nor have I gone back to read all the comments so if the following issue has already been discussed, please point it out.

Did you include an option to display the cardinality of the days, i.e., '2nd of March', '3rd of March', '4th of March', etc.

Thanks

 
Sean Smith (SSC)
Sean Smith (SSC)
Right there with Babe
Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)

Group: General Forum Members
Points: 739 Visits: 990
No, not yet. Very few changes in a long time actually. SSC just decided to re-feature it despite nothing really new in the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search