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:
I just ran the two statements from SRINIVAS... and they gave different results for July 2016. The first one gave 07/30/2016 and the second gave 07/31/2016. I can't exactly figure out why, but I wanted to report the results.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.