RE: AW: Embedded SQL formatting dates

-- Holidays are not included, because they are different in all countries and
--sometimes even different in different states within the same country.

The advantage of the DATE_DIM design over the calendar table is that you can group by a single column if you need year/week aggregation.
The same is true for any of the Sequence columns: Month, Week, Quarter, Period.

If you are going to spend the time and resources to create a date table, it makes more sense to me to have all date related attributes contained in it. Omitting any would just make it unusable in some scenarios.

When we added it, the DATE_DIM table was needed for two things. First, it was needed to do simple date conversion. Second, it was needed as a helper table for analysis.

We needed to accommodate date related attributes like fiscal year, fiscal week, fiscal quarter, and flags like Last Full Week of Month. These were used in analysis and reporting systems.


-- For all those who want to create an date table with Dates in different formats.
If all you need/want to do is basic date conversions, then Birgitta's version is great.
If you need to do more in depth analysis and reporting, then you might need to look into adding additional columns.

Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.