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

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.

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

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;

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>

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.

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.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

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

OR

DRossmann 1 comment Joined 11/11
14 Jul 2016

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.