Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Assaf.

Asked: October 20, 2009 - 2:26 am UTC

Last updated: March 14, 2022 - 3:42 am UTC

Version: 11.1.0

Viewed 10K+ times! This question is

You Asked

Hi
I'm trying to find a way to change a date, meaning change a year or a month within a given date.
The only results I got while searching the net for this question were to use the 'REPLACE' function which manipulates strings. And I need a natural way of dealing with date.
Thanks in advance
Assaf.

and Tom said...


You would not use replace, replace would be the worst way.


You can either

a) use date arithmetic
b) use builtin functions
c) use to_char/to_date


Let us say you wanted to make a date have a year that is two years less than the current value:

ops$tkyte%ORA11GR2> select sysdate, add_months( sysdate, -24 ) from dual;

SYSDATE              ADD_MONTHS(SYSDATE,-
-------------------- --------------------
21-oct-2009 12:04:59 21-oct-2007 12:04:59


Say you wanted to make a date field have the same day and year it currently has, but you wanted the month to be AUGUST

ops$tkyte%ORA11GR2> select sysdate, to_date( '08'||to_char(sysdate,'DDYYYYHH24MISS'), 'MMDDYYYYHH24MISS' ) from dual;

SYSDATE              TO_DATE('08'||TO_CHA
-------------------- --------------------
21-oct-2009 12:06:24 21-aug-2009 12:06:24



You would extract from the current date the values you didn't want to change (DDYYYYHH24MISS for me) and add in the bit you wanted to change (we wanted august, month 8) and convert back into a date.



Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Changing dates

Assaf Vizner, October 22, 2009 - 5:56 am UTC

Thanks Tom
your response was quick and very useful.

Very useful

Sarah, February 20, 2012 - 12:18 am UTC

Just what i was looking for. Thank you.

VERY USEFUL

SAURABH, June 22, 2017 - 2:51 pm UTC

THANKS TOM

Please Clarify the Solution

A reader, June 19, 2019 - 6:51 pm UTC

Please include a description of how changing the month portion of the date works. In my situation, I need to change the year. Since your example does not include more detailed information that explains how this works, I'm not able to adapt this.

My specific need is that I need to change the year to 2 years after to today's date.
Chris Saxon
June 20, 2019 - 4:03 pm UTC

Please clarify what you're trying to do and why the example doesn't help!

If you want to add 2 years to today's date, just add 24 months to the date...

Want to convert a birth date to a current birthday

Joy, March 13, 2022 - 7:09 pm UTC

I need to take a birth year on a birthdate and convert it to the current year. Is this possible? I need to be able to use birthday as a way to select people from a current date range of birthdays. The below code works until you try to span December of one year to January of the next.

<code>and TO_CHAR(X.date_of_birth,'MMDD') between TO_CHAR(:pFromDate,'MMDDYYYY') and TO_CHAR(:pToDate,'MMDDYYYY')


Connor McDonald
March 14, 2022 - 3:42 am UTC

Here's an example where I've already converted the dates to MMDD

SQL> create table t ( d varchar2(10), lo varchar2(10), hi varchar2(10));

Table created.

SQL>
SQL> insert into t values ('0224','0131','0313');

1 row created.

SQL> insert into t values ('0224','1131','0313');

1 row created.

SQL> insert into t values ('0224','0615','0913');

1 row created.

SQL>
SQL> select *
  2  from  t
  3  where ( lo < hi and d between lo and hi )
  4  or (  lo > hi and ( d between lo and '1231' or d between '0101' and hi ) );

D          LO         HI
---------- ---------- ----------
0224       0131       0313
0224       1131       0313


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.