How to get second Wednesday of the month

Question

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

Can anyone help me?

Answer 1

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

Answer 2

extract week day number and month day number then compare with 3 as each Wednesday day will 3 and 2nd Wednesday day month day will be greater than 8 and less than 13 and then use case when

select case when extract(dow from  now())=3 and
  EXTRACT(DAY FROM  now())>7 and EXTRACT(DAY FROM  now())<=13
  then 'it is 2nd wednesday' else 'not';