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?
Need to add days to a date, excluding SAT and SUN
Moderator: NorbertKrupa
Need to add days to a date, excluding SAT and SUN
Have a GREAT day!
- JimKnicely
- 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
Sarah,
Adding 12 days including Saturdays and Sundays:
Easy
Here is ONE possible solution for adding 12 days excluding Saturdays and Sundays:
Not so "easy"
Let's see if anyone else in the community has any ideas!
Adding 12 days including Saturdays and Sundays:
Code: Select all
dbadmin=> SELECT '2013-11-14'::DATE + 12;
?column?
------------
2013-11-26
(1 row)
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)
Let's see if anyone else in the community has any ideas!
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.