Question 403656 in PostgreSQL Question posted in
The official documentation can be found here.

Compare 2 dates to determine same month in Postgresql

I have two dates and I need to check that these two dates are within the same month of the same year.

I have the following solution

select to_char(:date1::date, 'yyyymm')::int2 = to_char(:date2::date, 'yyyymm')::int2

who has a better solution?

Tags:

2

Answers


  1. 0

    how about using date_trunc() which compares month year:

    SELECT date_trunc('month',date1) = date_trunc('month',date2)

    and if you only need same month regardless ofyear you can use date_part:

    SELECT date_part('month',date1) = date_part('month',date2)
    Login or Signup to reply.
  2. 0

    There are different ways to do this. Below are few of them depending on does the month should be within the same year or not.

    --    S a m p l e    D a t a :
Create Table tbl ( id Int, date_1 Date, date_2 Date );
Insert Into tbl VALUES ( 1, '2022-01-01', '2022-01-31' ),
                       ( 2, '2022-01-01', '2023-01-31' ),
                       ( 3, '2024-01-01', '2024-03-31' );

    1. same month – same year

    Select   *
From     tbl
Where    To_Char(date_1, 'yyyymm') = To_Char(date_2, 'yyyymm')

    Select   *
From     tbl
Where    Date_Trunc('Month', date_1) = Date_Trunc('Month', date_2)

    Select   *
From     tbl
Where    Extract(YEAR From date_1) = Extract(YEAR From date_2) And 
         Extract(MONTH From date_1) = Extract(MONTH From date_2)

    Select   *
From     tbl
Where    DATE_PART('YEAR', date_1) = DATE_PART('YEAR', date_2) And
         DATE_PART('MONTH', date_1) = DATE_PART('MONTH', date_2) 

    /*    R e s u l t :   (1)
id  date_1      date_2
--  ----------  ----------
 1  2022-01-01  2022-01-31    */

    2. same month – any year

    Select   *
From     tbl
Where    To_Char(date_1, 'mm') = To_Char(date_2, 'mm')

    Select   *
From     tbl
Where    Extract(MONTH From date_1) = Extract(MONTH From date_2) 

    Select   *
From     tbl
Where    DATE_PART('MONTH', date_1) = DATE_PART('MONTH', date_2) 

    /*    R e s u l t :   (2)
id  date_1      date_2
--  ----------  ----------
 1  2022-01-01  2022-01-31
 2  2022-01-01  2023-01-31    */

    See the fiddle here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search