I recently made a comment and then a contribution to a Dev posting about calculating dates differing by month. See that at:
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.
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:
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.
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:
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:
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:
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.
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.
Top comments (1)
Postscript
The Interval Elephant in the Room
BTW I did deliberately overlook INTERVAL options. Teradata has long supported these but I'll be the first to admit I've never willingly used them.
SQL quirks of mine
I also deliberately didn't explain some oddities in my SQL code, so I'll now describe those. These are just part of my coding habits, that I do without thinking not to.
Multiple row counts
This is my stock triple set of counts to code, before I even get around to choosing which to keep. In this case I was too lazy to edit the code or the outputs, so all three are still there.
Of course, quite often those are all the same - as is the case in all these examples. By habitually writing all three I have a habit of checking that they are what I expect, as well which I really want to use.
Maximum If Different
Again, another habit, although because I knew I was going to do an article about this code I chose to leave the ordinary MAX usage in place.
The idea here is to make it visually clear in which situations the MIN and MAX produce the same number. This is done with the NULLIF function/expression* - and has the effect of producing a NULL if the MAX is the same as the MIN.
Until you get used to it, the NULLIF seems to read awkwardly, so perhaps read the documentation for it and practice using it. It can be very handy to be familiar with.