1

Is there a way to find the last working day of last month? I know I can get last day of last month with SELECT (date_trunc('month', now())::date - 1), but how do I get the last "weekday"?

rose
  • 657
  • 2
  • 10
  • 20
  • 1
    Possible duplicate of [Calculate closest working day in Postgres](https://stackoverflow.com/questions/9743810/calculate-closest-working-day-in-postgres) – Emond Jun 27 '18 at 18:25
  • Feed this last day of the month to the query in the duplicate: https://stackoverflow.com/questions/9743810/calculate-closest-working-day-in-postgres – Emond Jun 27 '18 at 18:26

2 Answers2

2

I believe this does what you want:

select (date_trunc('month', current_date) + interval '1 month' -
        (case extract(dow from date_trunc('month', current_date) + interval '1 month')
              when 0 then 2
              when 1 then 3
              else 1
         end) * interval '1 day'
       ) as last_weekday_in_month

Such requests often suggest the need for a calendar table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Nice. However this returns the last week day of the current month. OP wants the last weekday of the *last* month. – Don Seiler Jun 27 '18 at 18:34
0

Piggy-backing off of Gordon's answer, I think this is what you want:

SELECT (
    date_trunc('month', current_date) - interval '1 day' -
    (case extract(dow from date_trunc('month', current_date) - interval '1 day')
         when 0 then 2
         when 6 then 1
         else 0
      end) * interval '1 day'
)::date as last_weekday_in_last_month;

Assuming your weekends are 0 (Sunday) and 6 (Saturday). It uses OP's original logic to find the last date of the last month, then Gordon's CASE logic to subtract more days if the last date is 0 or 6.

Don Seiler
  • 460
  • 5
  • 16