SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Todd Fifield / Calendar Tables II - The Anchor / Latest Posts

RE: Calendar Tables II - The Anchor

Jeff Moden — Sat, 04 Jun 2011 13:25:28 GMT

Almost forgot... I love the idea of having the NextDay column in there because it makes it so easy to join on dates with times while still being SARGable in the Joins and Predicates. ;-)

RE: Calendar Tables II - The Anchor

Jeff Moden — Sat, 04 Jun 2011 13:19:19 GMT

[quote][b]Knut Boehnert (8/16/2010)[/b][hr]Nearly excellent.Two things that made it only good for me is the "typical" fact that the date calculation works only for the US (and Canada). The other is the RBAR in the creation of the date table and the hard coded week day names.Suggestion for the date table creation below:[code]-- Set Language 'English' Set Language 'British'-- Set Language 'German'-- Set Language 'Spanish'-- Set Language 'Italian'-- Set Language 'French'---- execute sp_helplanguage -- documentation--INSERT INTO CalDay--(-- DayDate,-- NextDay,-- DayNum,-- NameOfDay--)SELECT -- add days to anchor date, then add number of years to that calculation DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime))) As DayDate, DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number, Cast('1900-01-01' As datetime))) As NextDay, Datepart(Weekday, DateAdd(yy, Years.YearNumber, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) As DayNum, DateName(Weekday, DateAdd(yy, Years.YearNumber, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) As NameOfDayFROM dbo.Numbers JulianDays Cross Join ( SELECT Number As YearNumber FROM dbo.Numbers WHERE Number Between 2000 And 2011 ) YearsWHERE -- ensure that the result is actually a date (safety check) IsDate(DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) = 1 AND -- don't generate double, one year always has 365 days JulianDays.Number Between 1 and 365 -- add a day for years modulo by 4, but not by 100, except when modulo by 400 -- deduct 1 or 0 from 1 if modulo by 4 = if leapyear 1 - 0 = 1 --> one extra day -- add 1 if modulo by 100/400 = if 1900,2100,2200 --> 1 - 0 + 0 - 1 --> no extra day -- if 2000, 2400, 2800 --> 1 - 0 + 0 - 0 --> one extra day + (1 - Sign(Years.YearNumber % 4) + Sign(Years.Yearnumber % 100) - Sign(Years.YearNumber % 400)) --AND -- Year(DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) < 2012Order By DayDate[/code]Certainly only works if there is dbo.Numbers around (how to at http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/). The beauty of above beast is that it handles date names simply by the language that is selected and the week day numbers too.One option to clear up the code is to create an intermediate step of a temporary table that holds just the date itself, then pass this temporary table to all the functions involved and pass this result set into CalDay.Very nice and fun article to read otherwise. I really like "the boss".[/quote]Your code labels the 1st of January, 2000 as a Monday. Check a calendar and find out that it actually occurred on a Saturday. ;-)Also, with a Tally or Numbers table, there's just no need for the complexity nor the double hit on the Tally table. Have a look...[code="sql"]--===== Declare some obviously-named variables and preset themDECLARE @StartYear DATETIME, @EndYear DATETIME; SELECT @StartYear = '2000', @EndYear = '2011';--===== Return the mini-date calendarWITHcteDays AS( SELECT DayDate = DATEADD(dd,t.N-1,@StartYear) FROM dbo.Tally t --works for zero and unit based Tally tables in this case WHERE t.N BETWEEN 1 AND DATEDIFF(dd,@StartYear,DATEADD(yy,1,@EndYear))) SELECT DayDate, NextDay = DATEADD(dd,1,DayDate), DayNum = DATEPART(dw,DayDate+@@DATEFIRST-1), --Doesn't care what DATEFIRST is set to. NameOfDay = DATENAME(dw,DayDate) FROM cteDays;[/code]

RE: Calendar Tables II - The Anchor

Jeff Moden — Sat, 04 Jun 2011 12:42:42 GMT

[quote][b]alen teplitsky (8/18/2010)[/b][hr]nice articlei tried using it today and the only problem was that my source data had hours and minutes and it would have taken too long to figure out a way to join it properly[/quote]I know this is an old post but figured I'd show how easy that join actually is thanks to Todd's forsight in including a "NextDay" column ...[code="sql"] SELECT whatever FROM dbo.yourtable yt INNER JOIN dbo.Calendar c ON yt.YourDate >= c.DayDate AND yt.YourDate < c.NextDay[/code]

RE: Calendar Tables II - The Anchor

alen teplitsky — Wed, 18 Aug 2010 12:00:40 GMT

nice articlei tried using it today and the only problem was that my source data had hours and minutes and it would have taken too long to figure out a way to join it properly

RE: Calendar Tables II - The Anchor

SQLRNNR — Mon, 16 Aug 2010 10:07:10 GMT

Thanks for sharing this with us.

RE: Calendar Tables II - The Anchor

Nadrek — Mon, 16 Aug 2010 09:07:47 GMT

In memento of the "as compact as possible", change DayID to smalldatetime and, if necessary, start at -32768 (there are exactly 65,536 days available to the SMALLDATETIME data type).DayNum should be TINYINTNameOfDay should be VARCHAR(9)Personally, I don't agree with having NextDay in there, but that's going to depend on what kind of data you're joining to.

RE: Calendar Tables II - The Anchor

Knut Boehnert — Mon, 16 Aug 2010 03:44:48 GMT

Nearly excellent.Two things that made it only good for me is the "typical" fact that the date calculation works only for the US (and Canada). The other is the RBAR in the creation of the date table and the hard coded week day names.Suggestion for the date table creation below:[code]-- Set Language 'English' Set Language 'British'-- Set Language 'German'-- Set Language 'Spanish'-- Set Language 'Italian'-- Set Language 'French'---- execute sp_helplanguage -- documentation--INSERT INTO CalDay--(-- DayDate,-- NextDay,-- DayNum,-- NameOfDay--)SELECT -- add days to anchor date, then add number of years to that calculation DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime))) As DayDate, DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number, Cast('1900-01-01' As datetime))) As NextDay, Datepart(Weekday, DateAdd(yy, Years.YearNumber, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) As DayNum, DateName(Weekday, DateAdd(yy, Years.YearNumber, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) As NameOfDayFROM dbo.Numbers JulianDays Cross Join ( SELECT Number As YearNumber FROM dbo.Numbers WHERE Number Between 2000 And 2011 ) YearsWHERE -- ensure that the result is actually a date (safety check) IsDate(DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) = 1 AND -- don't generate double, one year always has 365 days JulianDays.Number Between 1 and 365 -- add a day for years modulo by 4, but not by 100, except when modulo by 400 -- deduct 1 or 0 from 1 if modulo by 4 = if leapyear 1 - 0 = 1 --> one extra day -- add 1 if modulo by 100/400 = if 1900,2100,2200 --> 1 - 0 + 0 - 1 --> no extra day -- if 2000, 2400, 2800 --> 1 - 0 + 0 - 0 --> one extra day + (1 - Sign(Years.YearNumber % 4) + Sign(Years.Yearnumber % 100) - Sign(Years.YearNumber % 400)) --AND -- Year(DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) < 2012Order By DayDate[/code]Certainly only works if there is dbo.Numbers around (how to at http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/). The beauty of above beast is that it handles date names simply by the language that is selected and the week day numbers too.One option to clear up the code is to create an intermediate step of a temporary table that holds just the date itself, then pass this temporary table to all the functions involved and pass this result set into CalDay.Very nice and fun article to read otherwise. I really like "the boss".

RE: Calendar Tables II - The Anchor

Jason-299789 — Mon, 16 Aug 2010 02:11:17 GMT

Another nice example of using a date table, however when you assign the day name wouldnt you be better removing the Case statement and using the DateName(dw,<date>) function that is embeded in SQL, it removes confusion if a company doesnt use the default DATEFIRST.

Calendar Tables II - The Anchor

tfifield — Sat, 14 Aug 2010 16:57:16 GMT

Comments posted to this topic are about the item [B]Calendar Tables II - The Anchor[/B]