Home » SQL & PL/SQL » SQL & PL/SQL » how to replace date column (oracle 11gr2)
how to replace date column [message #675205] Thu, 14 March 2019 21:37 Go to next message
tommm
Messages: 2
Registered: March 2019
Junior Member
HI ALL,
could you please help me to write update statement?

i have one table and in that table i have birth_date column. i want to update that table with different date and month but keeping the year.

for example. . i want to convert (09/07/2019) to. '01/01/2019'. but in the table i have a lot of values all of them i want to update. i am able to write select statement but not a update.

select '01/01/'||to_char(birth_date, 'yyyy') from my_table;


Re: how to replace date column [message #675206 is a reply to message #675205] Thu, 14 March 2019 22:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

It is Worst Practice to store any date in either VARCHAR2 or number datatype.
only ever store date as DATE datatype.
In Oracle DATE datatype always contain a TIME component; even if it is 00:00:00 (as HH:MI:SS)

I'll assume that MY_TABLE.BIRTH_DATE is a DATE datatype.

UPDATE MY_TABLE SET BIRTH_DATE = TO_DATE('01/01/'||to_char(birth_date, 'yyyy'), 'MM/DD/YYYY'); -- which will change every row in MY_TABLE
Re: how to replace date column [message #675207 is a reply to message #675205] Fri, 15 March 2019 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

TRUNC:
SQL> select trunc(sysdate,'YEAR') from dual;
TRUNC(SYSDATE,'YEAR
-------------------
01/01/2019 00:00:00
Re: how to replace date column [message #675232 is a reply to message #675206] Fri, 15 March 2019 19:29 Go to previous messageGo to next message
tommm
Messages: 2
Registered: March 2019
Junior Member
Thank you BlackSwan

[Updated on: Fri, 15 March 2019 19:32]

Report message to a moderator

Re: how to replace date column [message #675399 is a reply to message #675232] Wed, 27 March 2019 07:22 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
easy enough

update my_table
set birth_date = to_date('01/01/'||TO_CHAR(birth_date,'YYYY'),'MM/DD/YYYY');

REPLACE THE "01/01" with any valid month/day that you want. if you only want to update a specific row use a where clause on the update

[Updated on: Wed, 27 March 2019 07:24]

Report message to a moderator

Re: how to replace date column [message #675400 is a reply to message #675399] Wed, 27 March 2019 07:50 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's exactly what BlackSwan posted above.
Re: how to replace date column [message #675401 is a reply to message #675400] Wed, 27 March 2019 08:24 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And better use TRUNC when you want to truncate.
Would you use something like TO_NUMBER(TO_CHAR(111.3,'999')) to truncate a number?

Previous Topic: Duplicate Insertion while executing Procedure
Next Topic: Error in materialized view refresh path
Goto Forum:
  


Current Time: Thu Mar 28 20:41:35 CDT 2024