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: 26451
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: 66249
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 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

Previous Topic: Performance of Virtual column referencing another table!
Next Topic: trunc function and inner joins
Goto Forum:
  


Current Time: Wed Mar 20 06:03:47 CDT 2019