Over the years I have had some of the strangest / most complex requirements for data querying / reporting that you could imagine. The most difficult often involves some type of strange business rule or requirement surrounding a date parameter, logic, range, etc. I've put this code together in order to pre-calculate many elements of a date's characteristics and be able to quickly determine various date characteristics / values without the need to break my head every time things get hairy.
To run it, simply choose the database in which you want the table to reside, populate the @vDate_Start and @vDate_End variables with the date range you want populated in the calendar, and execute.
Below is a listing of the output fields and their description using a date of 10/06/2009 (MM/DD/YYYY) as the reference example (the code should compensate for how any SQL Server instance is set up to handle the internal settings for start / end of week, weekdays, etc.). All values after the calendar_date field are specific to the date value found in each individual record.
- calendar_date: calendar date value (2009-10-06 00:00:00.000)
- calendar_year: year portion of the date (2009)
- calendar_month: month portion of the date (10)
- calendar_day: day portion of the date (6)
- calendar_quarter: quarter in which the date value falls under (4)
- first_day_in_week: first day of the week in which the date value is found (2009-10-04 00:00:00.000)
- last_day_in_week: last day of the week in which the date value is found (2009-10-10 00:00:00.000)
- is_week_in_same_month: is the first_day_in_week and last_day_in_week value contained within the same month - Boolean (1)
- first_day_in_month: first day of the month (2009-10-01 00:00:00.000)
- last_day_in_month: last day of the month (2009-10-31 00:00:00.000)
- is_last_day_in_month: is the date value the last day of the month (0)
- first_day_in_quarter: first day of the quarter (2009-10-01 00:00:00.000)
- last_day_in_quarter: last day of the quarter (2009-12-31 00:00:00.000)
- is_last_day_in_quarter: is the date value the last day of the quarter (0)
- day_of_week: day of the week (3)
- week_of_month: week of the month (2)
- week_of_quarter: week of the quarter (2)
- week_of_year: week of the year (41)
- days_in_month: total days in the month (31)
- month_days_remaining: number of days remaining in the month (25)
- weekdays_in_month: number of weekdays in the the month (22)
- month_weekdays_remaining: number of weekdays remaining in the month (18)
- month_weekdays_completed: number of weekdays completed in the month (4)
- days_in_quarter: total days in the quarter (92)
- quarter_days_remaining: number of days remaining in the quarter (86)
- quarter_days_completed: number of days completed in the quarter (6)
- weekdays_in_quarter: number of weekdays in the the quarter (66)
- quarter_weekdays_remaining: number of weekdays remaining in the quarter (62)
- quarter_days_completed: number of days completed in the quarter (6)
- day_of_year: number of days completed in the year (279)
- year_days_remaining: number of days remaining in the year (86)
- is_weekday: is the date a weekday - Boolean (1)
- is_leap_year: is the date contained within a leap year - Boolean (0)
- day_name: full name of the day (Tuesday)
- month_day_name_instance: number of occurrences of the day_name within the month up until and including the specified date (1)
- quarter_day_name_instance: number of occurrences of the day_name within the quarter up until and including the specified date (1)
- year_day_name_instance: number of occurrences of the day_name within the year up until and including the specified date (40)
- month_name: full name of the month (October)
- year_week: calendar_year and week_of_year (left padded with zeros) values concatenated (200941)
- year_month: calendar_year and calendar_month (left padded with zeros) values concatenated (200910)
- year_quarter: calendar_year and calendar_quarter (prefixed with a "Q") values concatenated (2009Q4)
