Calculate date with same week day.

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.)

[sourcecode language=”sql”]
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
,newadj
,to_char(base_date + newadj, ‘DY DD-MON-YY’) new_date
FROM datecalc

[/sourcecode]