TheLazy.Dev
Portfolio GitHub

Blog

rdb |mysql |postgres |sqlserver

What day is one month after 31st January? What day is one month before 31st March? I checked it with various RDBs.

kakisoft

kakisoft

Posted on April 14, 2022

What day is one month after 31st January? What day is one month before 31st March? I checked it with various RDBs.

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)"
Enter fullscreen mode Exit fullscreen mode

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)"
Enter fullscreen mode Exit fullscreen mode

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)"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

💖 💪 🙅 🚩
kakisoft
kakisoft

Posted on April 14, 2022

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

What day is one month after 31st January? What day is one month before 31st March? I checked it with various RDBs.
rdb What day is one month after 31st January? What day is one month before 31st March? I checked it with various RDBs.

April 14, 2022