Some Teradata SQL Fun with Dates Differing by Months
Tags: sql, teradata
Published: 2022-06-03 09:52:51

---
title: Some Teradata SQL Fun with Dates Differing by Months
published: true
description: Taking the issue of SQL dates and month differences a bit further
tags: SQL,Teradata
# cover_image: https://direct_url_to_image.jpg
# Use a ratio of 100:42 for best results.
---

I recently made a comment and then a contribution to a Dev posting about calculating dates differing by month. See that at:
- [What day is one month after 31st January? What day is one month before 31st March? I checked it with various RDBs.](https://dev.to/kakisoft/what-day-is-one-month-after-31st-january-what-day-is-one-month-before-31st-march-i-checked-it-with-various-rdbs-4j5k)

As my paid work happens to be on a Teradata datawarehouse, I added a Teradata equivalent method and result.

At the time, I had the feeling that this kind of calculation needs to be tested/checked over spans of up to four years - essentially to see if there are anomalies among various methods. By chance, today I had a spare moment that I then used to write out and run something more comprehensive.

## A Teradata Feature

I should point out that I'll be using a Teradata feature - its "System Calendar". This is a table/view that you can call on, that provides a set of calendar dates with several pre-derived values.

Here is an example of simply fetching it. I've usually presumed that it gets generated on demand - but for all I know it's really stored as an actual table. The truth is that I don't care about the distinction, I just use it and can rely on it being available.  

``` SQL
SELECT
	calendar_date ,
	year_of_calendar ,
	month_of_year
FROM
	sys_calendar.calendar
;
```

Note: it has other fields beyond these, I'm just quoting the ones I'll use here.

The main thing is that I can rely on it being a correctly valid set of dates. In practice, it is nearly always constrained with a WHERE clause to a range/type/set of desired dates.

## First Query - One Month Difference - Across All Dates

Here what I'll do is generalise from the specific examples in the other article, and then count how often the different variations occur.

In effect, we'll be generating triplets of dates:
- central date
- date at one month before
- date at one month after

In particular, our focus will be one what "day of the month" each of those derivations produces. We're really not interested in all the situations where all three dates are the same day number in the month.

- i.e. that a month before 6th June is the 6th May and one month after is the 6th July

So, we filter those out.

Then, we count up the combinations of interest. We'll aggregate across all the years covered by the system calendar, and look at the combinations of:
- which month, for the central date
- which day of the month for the central date
- which day of the month for one month before
- which day of the month for one month after

The SQL looks like:

``` SQL
SELECT
	month_of_year ,
	day_of_month ,
	MonthBefore_Day ,
	MonthAfter_Day ,
	COUNT(*) AS Rw_Cnt ,
	COUNT( calendar_date ) AS Day_Cnt ,
	COUNT( DISTINCT calendar_date ) AS Day_Ctd ,
	MIN( year_of_calendar ) AS Min_Year ,
	MAX( year_of_calendar ) AS Max_Year ,
	NULLIF( MAX( year_of_calendar ), Min_Year ) AS Mxd_Year 
FROM
	( -- InterestingDays
		SELECT
			calendar_date ,                
			year_of_calendar ,
			month_of_year ,                
			day_of_month ,                 
			ADD_MONTHS( calendar_date, 1 ) AS MonthBefore_Dt , 
			EXTRACT( DAY FROM ADD_MONTHS( calendar_date, -1 ) ) AS MonthBefore_Day ,
			ADD_MONTHS( calendar_date, 1 ) AS MonthAfter_Dt , 
			EXTRACT( DAY FROM ADD_MONTHS( calendar_date, 1 ) ) AS MonthAfter_Day 
		FROM
			sys_calendar.calendar 
		WHERE
			NOT ( ( day_of_month = MonthBefore_Day ) AND ( day_of_month = MonthAfter_Day ) AND ( MonthBefore_Day = MonthAfter_Day ) )
		) AS InterestingDays
GROUP BY
	month_of_year ,                
	day_of_month ,                 
	MonthBefore_Day ,
	MonthAfter_Day 
ORDER BY
	month_of_year ,                
	day_of_month ,                 
	MonthBefore_Day ,
	MonthAfter_Day 
;
```

and here is what that produces:

| month\_of\_year | day\_of\_month | MonthBefore\_Day | MonthAfter\_Day | Rw\_Cnt | Day\_Cnt | Day\_Ctd | Min\_Year | Max\_Year | Mxd\_Year |
| --------------- | -------------- | ---------------- | --------------- | ------- | -------- | -------- | --------- | --------- | --------- |
| 1               | 29             | 29               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 1               | 30             | 30               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 1               | 30             | 30               | 29              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 1               | 31             | 31               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 1               | 31             | 31               | 29              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 3               | 29             | 28               | 29              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 3               | 30             | 28               | 30              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 3               | 30             | 29               | 30              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 3               | 31             | 28               | 30              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 3               | 31             | 29               | 30              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 5               | 31             | 30               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 7               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 8               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 10              | 31             | 30               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 12              | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |


All well and good.

## Extend analysis to multiple month spans of interest

For the next part, I'll make another use of the Teradata system calendar. This time I'm merely going to use it as  row generator.

The general method is as follows, in this case to generate five rows. Note that I use yet another column this time: "day_of_calendar", which begins at one.

``` SQL
SELECT
	day_of_calendar AS N
FROM
	sys_calendar.calendar 
WHERE
	day_of_calendar BETWEEN 1 AND 5
;
```

While that only generates a list of numbers, it's quite straightforward to use a CASE expression to set custom values instead.

Such as:

``` SQL
SELECT
	CASE 
		WHEN day_of_calendar = 1 THEN 1
		WHEN day_of_calendar = 2 THEN 6
		WHEN day_of_calendar = 3 THEN 12
		WHEN day_of_calendar = 4 THEN 24
		WHEN day_of_calendar = 5 THEN 48
		END AS N
FROM
	sys_calendar.calendar 
WHERE
	day_of_calendar BETWEEN 1 AND 5
;
```

Then, to use that set of numbers (of months as we'll use it) we:
- add it to the query as a derived table - called "Make_N"
- link Make_N as a CROSS JOIN - so that we'll get the same analysis as before but now for every different number of months
- replace our number 1 usage in the ADD_MONTHS functions with the number from Make_N - note that for clarity I've written those as zero plus and zero minus N
- add N to the list of GROUP BY fields - so that we can see the outcomes per different months difference

Here we go:

``` SQL
SELECT
	N ,
	month_of_year ,                
	day_of_month ,                 
    MonthBefore_Day ,
    MonthAfter_Day ,
	COUNT(*) AS Rw_Cnt ,
	COUNT( calendar_date ) AS Day_Cnt ,
	COUNT( DISTINCT calendar_date ) AS Day_Ctd ,
	MIN( year_of_calendar ) AS Min_Year ,
	MAX( year_of_calendar ) AS Max_Year ,
	NULLIF( MAX( year_of_calendar ), Min_Year ) AS Mxd_Year 
FROM
	( -- InterestingDays
		SELECT
			Make_N.N ,
			calendar_date ,                
			year_of_calendar ,
			month_of_year ,                
			day_of_month ,                 
		    ADD_MONTHS( calendar_date, 0 - Make_N.N ) AS MonthBefore_Dt , 
		    EXTRACT( DAY FROM ADD_MONTHS( calendar_date, 0 - Make_N.N ) ) AS MonthBefore_Day ,
		    ADD_MONTHS( calendar_date, 0 + Make_N.N ) AS MonthAfter_Dt , 
		    EXTRACT( DAY FROM ADD_MONTHS( calendar_date, 0 + Make_N.N ) ) AS MonthAfter_Day 
		FROM
			sys_calendar.calendar 
			CROSS JOIN
			( -- Make_N
				SELECT
					CASE 
						WHEN day_of_calendar = 1 THEN 1
						WHEN day_of_calendar = 2 THEN 6
						WHEN day_of_calendar = 3 THEN 12
						WHEN day_of_calendar = 4 THEN 24
						WHEN day_of_calendar = 5 THEN 48
						END AS N
				FROM
					sys_calendar.calendar 
				WHERE
					day_of_calendar BETWEEN 1 AND 5
				) AS Make_N					
		WHERE
			NOT ( ( day_of_month = MonthBefore_Day ) AND ( day_of_month = MonthAfter_Day ) AND ( MonthBefore_Day = MonthAfter_Day ) )
		) AS InterestingDays
GROUP BY
	N ,
	month_of_year ,                
	day_of_month ,                 
    MonthBefore_Day ,
    MonthAfter_Day 
ORDER BY
	ABS( N ) ,
	N ,
	month_of_year ,                
	day_of_month ,                 
    MonthBefore_Day ,
    MonthAfter_Day 
;
```

and here is what that produces:

| N  | month\_of\_year | day\_of\_month | MonthBefore\_Day | MonthAfter\_Day | Rw\_Cnt | Day\_Cnt | Day\_Ctd | Min\_Year | Max\_Year | Mxd\_Year |
| -- | --------------- | -------------- | ---------------- | --------------- | ------- | -------- | -------- | --------- | --------- | --------- |
| 1  | 1               | 29             | 29               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 1  | 1               | 30             | 30               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 1  | 1               | 30             | 30               | 29              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 1  | 1               | 31             | 31               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 1  | 1               | 31             | 31               | 29              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 1  | 3               | 29             | 28               | 29              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 1  | 3               | 30             | 28               | 30              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 1  | 3               | 30             | 29               | 30              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 1  | 3               | 31             | 28               | 30              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 1  | 3               | 31             | 29               | 30              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 1  | 5               | 31             | 30               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 1  | 7               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 1  | 8               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 1  | 10              | 31             | 30               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 1  | 12              | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 6  | 3               | 31             | 30               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 6  | 5               | 31             | 30               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 6  | 8               | 29             | 28               | 28              | 103     | 103      | 103      | 1,900     | 2,100     | 2,100     |
| 6  | 8               | 29             | 28               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 6  | 8               | 29             | 29               | 28              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 6  | 8               | 30             | 28               | 28              | 103     | 103      | 103      | 1,900     | 2,100     | 2,100     |
| 6  | 8               | 30             | 28               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 6  | 8               | 30             | 29               | 28              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 6  | 8               | 31             | 28               | 28              | 103     | 103      | 103      | 1,900     | 2,100     | 2,100     |
| 6  | 8               | 31             | 28               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 6  | 8               | 31             | 29               | 28              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 6  | 10              | 31             | 30               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 6  | 12              | 31             | 30               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 12 | 2               | 29             | 28               | 28              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 24 | 2               | 29             | 28               | 28              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 48 | 2               | 29             | 28               | 29              | 1       | 1        | 1        | 1,904     | 1,904     | ?         |
| 48 | 2               | 29             | 29               | 28              | 1       | 1        | 1        | 2,096     | 2,096     | ?         |

## Another Improvement - Twelve and Two and Four 

Looking at the previous output I did wonder whether I was perhaps missing something interesting for some other combinations.

I decided to change my months-apart set to have from one to twelve months, then two years and four years.

Here is my new inner month-number generating SQL:

``` SQL
SELECT
	CASE 
		WHEN day_of_calendar <= 12 THEN day_of_calendar
		WHEN day_of_calendar = 13 THEN 24
		WHEN day_of_calendar = 14 THEN 48
		END AS N
FROM
	sys_calendar.calendar 
WHERE
	day_of_calendar BETWEEN 1 AND 14
;
```

and here it is plugged into the previous query.

``` SQL
SELECT
	N ,
	month_of_year ,                
	day_of_month ,                 
    MonthBefore_Day ,
    MonthAfter_Day ,
	COUNT(*) AS Rw_Cnt ,
	COUNT( calendar_date ) AS Day_Cnt ,
	COUNT( DISTINCT calendar_date ) AS Day_Ctd ,
	MIN( year_of_calendar ) AS Min_Year ,
	MAX( year_of_calendar ) AS Max_Year ,
	NULLIF( MAX( year_of_calendar ), Min_Year ) AS Mxd_Year 
FROM
	( -- InterestingDays
		SELECT
			Make_N.N ,
			calendar_date ,                
			year_of_calendar ,
			month_of_year ,                
			day_of_month ,                 
		    ADD_MONTHS( calendar_date, 0 - Make_N.N ) AS MonthBefore_Dt , 
		    EXTRACT( DAY FROM ADD_MONTHS( calendar_date, 0 - Make_N.N ) ) AS MonthBefore_Day ,
		    ADD_MONTHS( calendar_date, 0 + Make_N.N ) AS MonthAfter_Dt , 
		    EXTRACT( DAY FROM ADD_MONTHS( calendar_date, 0 + Make_N.N ) ) AS MonthAfter_Day 
		FROM
			sys_calendar.calendar 
			CROSS JOIN
			( -- Make_N
				SELECT
					CASE 
						WHEN day_of_calendar <= 12 THEN day_of_calendar
						WHEN day_of_calendar = 13 THEN 24
						WHEN day_of_calendar = 14 THEN 48
						END AS N
				FROM
					sys_calendar.calendar 
				WHERE
					day_of_calendar BETWEEN 1 AND 14
				) AS Make_N					
		WHERE
			NOT ( ( day_of_month = MonthBefore_Day ) AND ( day_of_month = MonthAfter_Day ) AND ( MonthBefore_Day = MonthAfter_Day ) )
		) AS InterestingDays
GROUP BY
	N ,
	month_of_year ,                
	day_of_month ,                 
    MonthBefore_Day ,
    MonthAfter_Day 
ORDER BY
	ABS( N ) ,
	N ,
	month_of_year ,                
	day_of_month ,                 
    MonthBefore_Day ,
    MonthAfter_Day 
;
```

and here is what that produces:

| N  | month\_of\_year | day\_of\_month | MonthBefore\_Day | MonthAfter\_Day | Rw\_Cnt | Day\_Cnt | Day\_Ctd | Min\_Year | Max\_Year | Mxd\_Year |
| -- | --------------- | -------------- | ---------------- | --------------- | ------- | -------- | -------- | --------- | --------- | --------- |
| 1  | 1               | 29             | 29               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 1  | 1               | 30             | 30               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 1  | 1               | 30             | 30               | 29              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 1  | 1               | 31             | 31               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 1  | 1               | 31             | 31               | 29              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 1  | 3               | 29             | 28               | 29              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 1  | 3               | 30             | 28               | 30              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 1  | 3               | 30             | 29               | 30              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 1  | 3               | 31             | 28               | 30              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 1  | 3               | 31             | 29               | 30              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 1  | 5               | 31             | 30               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 1  | 7               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 1  | 8               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 1  | 10              | 31             | 30               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 1  | 12              | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 2  | 1               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 2  | 4               | 29             | 28               | 29              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 2  | 4               | 30             | 28               | 30              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 2  | 4               | 30             | 29               | 30              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 2  | 7               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 2  | 8               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 2  | 12              | 29             | 29               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 2  | 12              | 30             | 30               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 2  | 12              | 30             | 30               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 2  | 12              | 31             | 31               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 2  | 12              | 31             | 31               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 3  | 1               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 3  | 3               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 3  | 5               | 29             | 28               | 29              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 3  | 5               | 30             | 28               | 30              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 3  | 5               | 30             | 29               | 30              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 3  | 5               | 31             | 28               | 31              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 3  | 5               | 31             | 29               | 31              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 3  | 7               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 3  | 8               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 3  | 11              | 29             | 29               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 3  | 11              | 30             | 30               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 3  | 11              | 30             | 30               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 3  | 12              | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 4  | 1               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 4  | 3               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 4  | 5               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 4  | 6               | 29             | 28               | 29              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 4  | 6               | 30             | 28               | 30              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 4  | 6               | 30             | 29               | 30              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 4  | 7               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 4  | 8               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 4  | 10              | 29             | 29               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 4  | 10              | 30             | 30               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 4  | 10              | 30             | 30               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 4  | 10              | 31             | 30               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 4  | 10              | 31             | 30               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 4  | 12              | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 5  | 1               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 5  | 7               | 29             | 28               | 29              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 5  | 7               | 30             | 28               | 30              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 5  | 7               | 30             | 29               | 30              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 5  | 7               | 31             | 28               | 31              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 5  | 7               | 31             | 29               | 31              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 5  | 9               | 29             | 29               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 5  | 9               | 30             | 30               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 5  | 9               | 30             | 30               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 6  | 3               | 31             | 30               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 6  | 5               | 31             | 30               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 6  | 8               | 29             | 28               | 28              | 103     | 103      | 103      | 1,900     | 2,100     | 2,100     |
| 6  | 8               | 29             | 28               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 6  | 8               | 29             | 29               | 28              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 6  | 8               | 30             | 28               | 28              | 103     | 103      | 103      | 1,900     | 2,100     | 2,100     |
| 6  | 8               | 30             | 28               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 6  | 8               | 30             | 29               | 28              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 6  | 8               | 31             | 28               | 28              | 103     | 103      | 103      | 1,900     | 2,100     | 2,100     |
| 6  | 8               | 31             | 28               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 6  | 8               | 31             | 29               | 28              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 6  | 10              | 31             | 30               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 6  | 12              | 31             | 30               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 7  | 1               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 7  | 7               | 29             | 29               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 7  | 7               | 30             | 30               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 7  | 7               | 30             | 30               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 7  | 7               | 31             | 31               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 7  | 7               | 31             | 31               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 7  | 9               | 29             | 28               | 29              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 7  | 9               | 30             | 28               | 30              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 7  | 9               | 30             | 29               | 30              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 8  | 1               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 8  | 3               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 8  | 5               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 8  | 6               | 29             | 29               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 8  | 6               | 30             | 30               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 8  | 6               | 30             | 30               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 8  | 7               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 8  | 8               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 8  | 10              | 29             | 28               | 29              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 8  | 10              | 30             | 28               | 30              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 8  | 10              | 30             | 29               | 30              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 8  | 10              | 31             | 28               | 30              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 8  | 10              | 31             | 29               | 30              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 8  | 12              | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 9  | 1               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 9  | 3               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 9  | 5               | 29             | 29               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 9  | 5               | 30             | 30               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 9  | 5               | 30             | 30               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 9  | 5               | 31             | 31               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 9  | 5               | 31             | 31               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 9  | 7               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 9  | 8               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 9  | 11              | 29             | 28               | 29              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 9  | 11              | 30             | 28               | 30              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 9  | 11              | 30             | 29               | 30              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 9  | 12              | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 10 | 1               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 10 | 4               | 29             | 29               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 10 | 4               | 30             | 30               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 10 | 4               | 30             | 30               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 10 | 7               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 10 | 8               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 10 | 12              | 29             | 28               | 29              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 10 | 12              | 30             | 28               | 30              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 10 | 12              | 30             | 29               | 30              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 10 | 12              | 31             | 28               | 31              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 10 | 12              | 31             | 29               | 31              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 11 | 1               | 29             | 28               | 29              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 11 | 1               | 30             | 28               | 30              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 11 | 1               | 30             | 29               | 30              | 49      | 49       | 49       | 1,905     | 2,097     | 2,097     |
| 11 | 1               | 31             | 28               | 31              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 11 | 1               | 31             | 29               | 31              | 49      | 49       | 49       | 1,905     | 2,097     | 2,097     |
| 11 | 3               | 29             | 29               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 11 | 3               | 30             | 30               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 11 | 3               | 30             | 30               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 11 | 3               | 31             | 30               | 28              | 152     | 152      | 152      | 1,900     | 2,100     | 2,100     |
| 11 | 3               | 31             | 30               | 29              | 49      | 49       | 49       | 1,903     | 2,095     | 2,095     |
| 11 | 5               | 31             | 30               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 11 | 7               | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 11 | 8               | 31             | 30               | 31              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 11 | 10              | 31             | 30               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 11 | 12              | 31             | 31               | 30              | 201     | 201      | 201      | 1,900     | 2,100     | 2,100     |
| 12 | 2               | 29             | 28               | 28              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 24 | 2               | 29             | 28               | 28              | 49      | 49       | 49       | 1,904     | 2,096     | 2,096     |
| 48 | 2               | 29             | 28               | 29              | 1       | 1        | 1        | 1,904     | 1,904     | ?         |
| 48 | 2               | 29             | 29               | 28              | 1       | 1        | 1        | 2,096     | 2,096     | ?         |

## Summary and Challenge

I'm inclined to say the data speaks for itself - particularly that what this is really telling us is how the Teradata ADD_MONTHS function operates.

- I'd have to be honest that I've been mainly on that platform for so long I probably take how it works - with ADD_MONTHS - as "normal". I vaguely remember seeing discussion about SQL dialects and engines varying in exactly what their methods for this produces. 

It would be interesting to see how the other platforms compare, but frankly I don't have any means to do that. Also, I can't even guess what the equivalents are for the Teradata system calendar.

So dear readers: over to you.