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?
Discussion
DGiabbai 2 comments Joined 07/04
04 Mar 2011

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).

RobG 5 comments Joined 09/10
04 Mar 2011

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.

bmcclernan 3 comments Joined 04/08
19 Apr 2011

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.

Bill McClernan

RobG 5 comments Joined 09/10
19 Apr 2011

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;

DGiabbai 2 comments Joined 07/04
19 Apr 2011

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...

12 May 2011

thanks for sharing this information. Learned something here, this room is very informative. Thanks to you, keep it up mate!

<a href=http://www.healthinsurancepricing.com/>Best California Health Insurance</a>

rajanimandava 5 comments Joined 01/11
24 May 2011

This is because of TD date storage as integer internally. Here is how you can see

sel cast(date as int);

chrysdw 1 comment Joined 10/11
23 Mar 2012

Which is better to use; date-date mod 100 or date-extract(day from date)? They will both give the same result.

ulrich 36 comments Joined 09/09
23 Mar 2012

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.

Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles

dnoeth 66 comments Joined 11/04
23 Mar 2012

Another reason, why "date MOD 100" is bad:
It will produce wrong results for any date before 1900.

Dieter

Dieter

23 Aug 2012

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;

You must sign in to leave a comment.