Determining Month-End Date for a Date Dimension

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Fan

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:

ADD_MONTHS(..., month_of_year)

Questions:

  • 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?
12 Comments
Enthusiast
I would have used a more generic and reusable expression like the following:

ADD_MONTHS(calendar_date - EXTRACT(DAY FROM calendar_date)+1, 1) - 1

This way you may use any date field from any table as source (or else the "current_day" variable) without the need to join to the syscalendar... (just replace "calendar_date" with the field you're referencing in the ELT).
Fan
Nice. Your logic produces the exact same result as mine.

I was populating the date dimension in our warehouse using Teradata's sys_calendar.CALENDAR table as a source. That's why I used those specific columns.
Enthusiast
For an even simpler calculation:
SELECT DATE-DATE MOD 100;
returns the last day of the previous month for any date. This may be more useful because it does not require the need for the sys_calendar table.
Fan
bmcclernan, that's a great tool to have available.

Here's a quick test showing the three calculations in this post:

SELECT
calendar_date,
ADD_MONTHS(calendar_date - day_of_year, month_of_year) AS LastOfMo1,
ADD_MONTHS(calendar_date - EXTRACT(DAY FROM calendar_date) + 1, 1) - 1 AS LastOfMo2,
calendar_date - calendar_date MOD 100 AS LastOfPrevMo
FROM sys_calendar.CALENDAR
WHERE calendar_date BETWEEN DATE '2011-01-01' AND DATE '2011-02-28'
ORDER BY calendar_date;
Enthusiast
same results as @bmcclernan 's solution for last day of previous month would be

SELECT DATE-extract(DAY from DATE);

that may make more sense in reading, but I suppose it'd be rather worst in perfomance...
thanks for sharing this information. Learned something here, this room is very informative. Thanks to you, keep it up mate!
Enthusiast
This is because of TD date storage as integer internally. Here is how you can see

sel cast(date as int);

Which is better to use; date-date mod 100 or date-extract(day from date)? They will both give the same result.
Senior Supporter
date-extract(day from date) as it should be clear for everybody what you really want where date mod 100 would need knowledge about TD internal representation of dates.
Junior Contributor
Another reason, why "date MOD 100" is bad:
It will produce wrong results for any date before 1900.

Dieter
SEL ADD_MONTHS(CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE),1);

OR

SEL ADD_MONTHS((CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE)+1),1)-1;
Enthusiast

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.