Calculate date with same week day.

April 8, 2010 at 2:44 pm | Posted in Oracle Developement, Oracle Tools | Leave a comment

I recently needed to move the dates in several tables in our development database in such a way that the days of the week remained the same, i.e. Mondays remain Mondays, Tuesdays remain Tuesdays, etc.

To do this I wrote a quick SQL statement that calculates how many days you need to add (or subtract) to a given date, based on an estimated value. For example you want to move Thursday, March 1st, 2007 forward by 365 days. So if you were to add 365 to that date you would end up with Friday, March 1st, 2008. The script would tell you that you should really only add 364 days, because then you will end up with another Thursday.

(For easier readablity I chose a factored subquery. But you could easily re-write it into a plain select statement.)

WITH datecalc AS
 (SELECT to_date('&base_date') base_date
        ,(to_date('&base_date') + (&adj)) newdate
        ,(&adj + to_char(to_date('&base_date'), 'D') -
         to_char(to_date('&base_date') + (&adj), 'D')) newadj
    FROM dual)
SELECT to_char(base_date, 'DY DD-MON-YY') base_date
      ,to_char(base_date + newadj, 'DY DD-MON-YY') new_date
  FROM datecalc


Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Entries and comments feeds.

%d bloggers like this: