Determining Month-End Date for a Date Dimension
How do you determine the month-end date when building a date dimension?
One of our ETL developers recently pointed out that our date dimension (which I had populated) had incorrect values for month-end date, so I had to find a way to reliably calculate that value.
Goal: For every date in the calendar, determine the date of the last day of that month. Example: for every day in March of 2011, the month-end date is 2011-03-31.
Here is the logic I ended up using:
SELECT ADD_MONTHS(calendar_date - day_of_year, month_of_year) FROM sys_calendar.CALENDAR
This part drops the calendar date back to 12/31 of the previous year:
calendar_date - day_of_year
Then this part adds the appropriate number of months:
- Do you have a better way to calculate month-end date?
- What issues have you found in populating a date dimension? How did you resolve them?