What day is one month after 31st January? What day is one month before 31st March? I checked it with various RDBs.
Tags: rdb, mysql, postgres, sqlserver
Published: 2022-04-14 11:22:17

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
```sql
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
```sql
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
```sql
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
```sql
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".