SELECT DATE_TRUNC('day', trade_date + INTERVAL '1 DAY') from trade_table;

I have dates that are all on Monday through Friday, but when I date_trunc a date that is on Saturday (e.g. taking weekday plus 1), it is rounded to Saturday. I want it to be rounded to the following Monday rather than Saturday. How can I do that?


If I understand right you want to add one day and if the result is Saturday or Sunday add 2 or 1 more days, so the result is the next working day. Then extracting first the day of the week (dow) and using a CASE expression, will do:

select date_trunc('day', 
                  trade_date + ( case extract(dow from trade_date)
                                   when 5 THEN 3     -- when Friday, add 3
                                   when 6 THEN 2     -- when Saturday, add 2
                                   else 1            -- any other day, add 1
                               ) * interval '1 day' )  
from trade_table ;
  • Thanks a lot, I've done a lot of Python and R, but I'm new to databases. – Wart Oct 7 '16 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.