Search code examples
sqlpostgresqldayofweek

How to get second Wednesday of the month

I need to know if current_date is the second Wednesday of the month with PostgreSQL.

Can anyone help me?

Solution

  • You can use EXTRACT to find the day of week and compare that to Wednesday (3rd day of week), and then also check that the day of the month is between the 8th and 14th (inclusive), which will make it the second Wednesday:

    select extract(dow from current_date) = 3 and
       extract(day from current_date) between 8 and 14;

    Output (today 7 Sep 2018)

    false

    Another example to demonstrate it working:

    select extract(dow from timestamp '2018-09-12') = 3 and
       extract(day from timestamp '2018-09-12') between 8 and 14;

    Output

    true