On 31/1, when you read this message like this, can you immediately think of what day it is?
"It will expire one month after the registration date."
Also, what about the following message on 31/3?
"One month's notice will be given."
I looked into this, but I couldn't find any legal provisions or international rules.
So, I decided to research it with various RDBs.
I tried with:
- MySQL
- PostgreSQL
- SQL Server
- Oracle
In all RDBs result were the same.
Result
| Contents | Result |
|---|---|
| 1 month after 28/1 | 28/2 |
| 1 month after 29/1 | 28/2 |
| 1 month after 30/1 | 28/2 |
| 1 month after 31/1 | 28/2 |
| 1 month before 28/3 | 28/2 |
| 1 month before 29/3 | 28/2 |
| 1 month before 30/3 | 28/2 |
| 1 month before 31/3 | 28/2 |
| 1 month after 28/1(leap year) | 28/2 |
| 1 month after 29/1(leap year) | 29/2 |
| 1 month after 30/1(leap year) | 29/2 |
| 1 month after 31/1(leap year) | 29/2 |
| 1 month before 28/3(leap year) | 28/2 |
| 1 month before 29/3(leap year) | 29/2 |
| 1 month before 30/3(leap year) | 29/2 |
| 1 month before 31/3(leap year) | 29/2 |
SQL statement
MySQL
ver：5.7
SELECT
DATE_ADD(CAST('2018/01/28' AS DATE), INTERVAL 1 MONTH) AS "1 month after 28/1"
,DATE_ADD(CAST('2018/01/29' AS DATE), INTERVAL 1 MONTH) AS "1 month after 29/1"
,DATE_ADD(CAST('2018/01/30' AS DATE), INTERVAL 1 MONTH) AS "1 month after 30/1"
,DATE_ADD(CAST('2018/01/31' AS DATE), INTERVAL 1 MONTH) AS "1 month after 31/1"
,DATE_ADD(CAST('2018/03/28' AS DATE), INTERVAL -1 MONTH) AS "1 month before 28/3"
,DATE_ADD(CAST('2018/03/29' AS DATE), INTERVAL -1 MONTH) AS "1 month before 29/3"
,DATE_ADD(CAST('2018/03/30' AS DATE), INTERVAL -1 MONTH) AS "1 month before 30/3"
,DATE_ADD(CAST('2018/03/31' AS DATE), INTERVAL -1 MONTH) AS "1 month before 31/3"
,DATE_ADD(CAST('2020/01/28' AS DATE), INTERVAL 1 MONTH) AS "1 month after 28/1(leap year)"
,DATE_ADD(CAST('2020/01/29' AS DATE), INTERVAL 1 MONTH) AS "1 month after 29/1(leap year)"
,DATE_ADD(CAST('2020/01/30' AS DATE), INTERVAL 1 MONTH) AS "1 month after 30/1(leap year)"
,DATE_ADD(CAST('2020/01/31' AS DATE), INTERVAL 1 MONTH) AS "1 month after 31/1(leap year)"
,DATE_ADD(CAST('2020/03/28' AS DATE), INTERVAL -1 MONTH) AS "1 month before 28/3(leap year)"
,DATE_ADD(CAST('2020/03/29' AS DATE), INTERVAL -1 MONTH) AS "1 month before 29/3(leap year)"
,DATE_ADD(CAST('2020/03/30' AS DATE), INTERVAL -1 MONTH) AS "1 month before 30/3(leap year)"
,DATE_ADD(CAST('2020/03/31' AS DATE), INTERVAL -1 MONTH) AS "1 month before 31/3(leap year)"
PostgreSQL
ver：9.6
SELECT
CAST('2018/01/28' AS DATE) + interval '1 months' AS "1 month after 28/1"
,CAST('2018/01/29' AS DATE) + interval '1 months' AS "1 month after 29/1"
,CAST('2018/01/30' AS DATE) + interval '1 months' AS "1 month after 30/1"
,CAST('2018/01/31' AS DATE) + interval '1 months' AS "1 month after 31/1"
,CAST('2018/03/28' AS DATE) + interval '-1 months' AS "1 month before 28/3"
,CAST('2018/03/29' AS DATE) + interval '-1 months' AS "1 month before 29/3"
,CAST('2018/03/30' AS DATE) + interval '-1 months' AS "1 month before 30/3"
,CAST('2018/03/31' AS DATE) + interval '-1 months' AS "1 month before 31/3"
,CAST('2020/01/28' AS DATE) + interval '1 months' AS "1 month after 28/1(leap year)"
,CAST('2020/01/29' AS DATE) + interval '1 months' AS "1 month after 29/1(leap year)"
,CAST('2020/01/30' AS DATE) + interval '1 months' AS "1 month after 30/1(leap year)"
,CAST('2020/01/31' AS DATE) + interval '1 months' AS "1 month after 31/1(leap year)"
,CAST('2020/03/28' AS DATE) + interval '-1 months' AS "1 month before 28/3(leap year)"
,CAST('2020/03/29' AS DATE) + interval '-1 months' AS "1 month before 29/3(leap year)"
,CAST('2020/03/30' AS DATE) + interval '-1 months' AS "1 month before 30/3(leap year)"
,CAST('2020/03/31' AS DATE) + interval '-1 months' AS "1 month before 31/3(leap year)"
SQL Server
ver：14.0
SELECT
DATEADD(MONTH, 1, CAST('2018/01/28' AS DATE)) AS "1 month after 28/1"
,DATEADD(MONTH, 1, CAST('2018/01/29' AS DATE)) AS "1 month after 29/1"
,DATEADD(MONTH, 1, CAST('2018/01/30' AS DATE)) AS "1 month after 30/1"
,DATEADD(MONTH, 1, CAST('2018/01/31' AS DATE)) AS "1 month after 31/1"
,DATEADD(MONTH, -1, CAST('2018/03/28' AS DATE)) AS "1 month before 28/3"
,DATEADD(MONTH, -1, CAST('2018/03/29' AS DATE)) AS "1 month before 29/3"
,DATEADD(MONTH, -1, CAST('2018/03/30' AS DATE)) AS "1 month before 30/3"
,DATEADD(MONTH, -1, CAST('2018/03/31' AS DATE)) AS "1 month before 31/3"
,DATEADD(MONTH, 1, CAST('2020/01/28' AS DATE)) AS "1 month after 28/1(leap year)"
,DATEADD(MONTH, 1, CAST('2020/01/29' AS DATE)) AS "1 month after 29/1(leap year)"
,DATEADD(MONTH, 1, CAST('2020/01/30' AS DATE)) AS "1 month after 30/1(leap year)"
,DATEADD(MONTH, 1, CAST('2020/01/31' AS DATE)) AS "1 month after 31/1(leap year)"
,DATEADD(MONTH, -1, CAST('2020/03/28' AS DATE)) AS "1 month before 28/3(leap year)"
,DATEADD(MONTH, -1, CAST('2020/03/29' AS DATE)) AS "1 month before 29/3(leap year)"
,DATEADD(MONTH, -1, CAST('2020/03/30' AS DATE)) AS "1 month before 30/3(leap year)"
,DATEADD(MONTH, -1, CAST('2020/03/31' AS DATE)) AS "1 month before 31/3(leap year)"
Oracle
ver：11g
SELECT
ADD_MONTHS(TO_DATE('2018/01/28'), 1) AS "1 month after 28/1"
,ADD_MONTHS(TO_DATE('2018/01/29'), 1) AS "1 month after 29/1"
,ADD_MONTHS(TO_DATE('2018/01/30'), 1) AS "1 month after 30/1"
,ADD_MONTHS(TO_DATE('2018/01/31'), 1) AS "1 month after 31/1"
,ADD_MONTHS(TO_DATE('2018/03/31'), -1) AS "1 month before 28/3"
,ADD_MONTHS(TO_DATE('2018/03/31'), -1) AS "1 month before 29/3"
,ADD_MONTHS(TO_DATE('2018/03/31'), -1) AS "1 month before 30/3"
,ADD_MONTHS(TO_DATE('2018/03/31'), -1) AS "1 month before 31/3"
,ADD_MONTHS(TO_DATE('2020/01/28'), 1) AS "1 month after 28/1(leap year)"
,ADD_MONTHS(TO_DATE('2020/01/29'), 1) AS "1 month after 29/1(leap year)"
,ADD_MONTHS(TO_DATE('2020/01/30'), 1) AS "1 month after 30/1(leap year)"
,ADD_MONTHS(TO_DATE('2020/01/31'), 1) AS "1 month after 31/1(leap year)"
,ADD_MONTHS(TO_DATE('2020/03/31'), -1) AS "1 month before 28/3(leap year)"
,ADD_MONTHS(TO_DATE('2020/03/31'), -1) AS "1 month before 29/3(leap year)"
,ADD_MONTHS(TO_DATE('2020/03/31'), -1) AS "1 month before 30/3(leap year)"
,ADD_MONTHS(TO_DATE('2020/03/31'), -1) AS "1 month before 31/3(leap year)"
FROM
DUAL
Extra
If you want to notify the expiration date, it may be better to use days instead of month, such as "The data retention period is 90 days".
Top comments (5)
One of the tests to consider for this kind of thing is the example person born on the 29th February. Thus the question is: what day is 12 months later and what day is 4 times 12 months later? The latter should come out as the 29th again.
As you might expect, this means that fully checking calendar functions requires a disturbing kind of thoroughness.
As I'm currently on leave, I can't chip in with Teradata equivalents to your tests.
Also, Teradata provide a built in generated calendar that makes thorough checking of these things easier.
In my work I've dealt with dates since before the 2000 century mark so I've had to check my understanding on both sides of that.
Thank you for comment!
I would love to know the test result of Teradata.
If you test it, please let me know!
I managed to find a spare moment to construct the Teradata equivalent, run it, and send myself the results.
I've used the Add_Months function as that's what most Teradata users would probably do. It's likely that one of the Interval methods would have worked, as Teradata supports most of the SQL standard, but I thought this was a more interesting thing to share.
And here's what that generated.
@geraldew
I really appreciate your kindness and you talking time for this research!
It helps me to understand more!
FYI - I've now written a new article that was prompted by this one. It seemed a bit too long to put here as a response, but in it I've given a link to here. It is also quite Teradata specific.