Calendar Tables II - The Anchor

-- 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 NameOfDay
FROM dbo.Numbers JulianDays
Cross Join
(
SELECT
Number As YearNumber
FROM dbo.Numbers
WHERE Number Between 2000 And 2011
) Years
WHERE -- 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)))) < 2012
Order By DayDate

Knut Boehnert (8/16/2010)

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:

-- 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 NameOfDay
FROM dbo.Numbers JulianDays
    Cross Join
    (
        SELECT
            Number As YearNumber
        FROM dbo.Numbers 
        WHERE Number Between 2000 And 2011
    ) Years
WHERE   -- 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)))) < 2012
Order By DayDate

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".

--===== Declare some obviously-named variables and preset them
DECLARE @StartYear DATETIME,
@EndYear DATETIME
;
SELECT @StartYear = '2000',
@EndYear = '2011'
;
--===== Return the mini-date calendar
WITH
cteDays 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
;