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