Need to add days to a date, excluding SAT and SUN

Moderator: NorbertKrupa

Post Reply
sarah
Intermediate
Intermediate
Posts: 77
Joined: Mon Aug 27, 2012 1:34 pm

Need to add days to a date, excluding SAT and SUN

Post by sarah » Thu Nov 14, 2013 5:11 pm

Hi gurus,

I have a need to add X days to a date but exclude adding any Saturday or Sundays. Today is 11/14/2013. I want to add 12 days. The result I would like is 12/01/2013 (Monday) and not 11/26/2013 (Tuesday).

Is there a simple way to accomplish this in Vertica SQL?
Have a GREAT day!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Need to add days to a date, excluding SAT and SUN

Post by JimKnicely » Thu Nov 14, 2013 8:02 pm

Sarah,

Adding 12 days including Saturdays and Sundays:

Code: Select all

dbadmin=> SELECT '2013-11-14'::DATE + 12;
  ?column?
------------
 2013-11-26
(1 row)
Easy :D

Here is ONE possible solution for adding 12 days excluding Saturdays and Sundays:

Code: Select all

dbadmin=> SELECT MAX(the_date)
dbadmin->   FROM (SELECT '2013-11-14'::DATE + rn the_date
dbadmin(>           FROM (SELECT row_number() OVER () rn
dbadmin(>                   FROM tables CROSS JOIN columns) foo
dbadmin(>                  WHERE DAYOFWEEK('2013-11-14'::DATE + rn) NOT IN (1, 7)
dbadmin(>          LIMIT 12) foo;
    MAX
------------
 2013-12-02
(1 row)
Not so "easy" :(

Let's see if anyone else in the community has any ideas!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica SQL”