REPLACE FUNCTION WITH DATE - LOSING TIMESTAMP

[Vandy02]

I am wanting to fix a date issue problem, but I keep losing the time.

startdate = 7/4/0004 11:00:07 PM

I want to replace the '0004' with '2004'.

When I run the following update statement I get

startdate = 7/4/2004

update labtrans_john
set startdate = replace(startdate, '0004', '2004')
where to_char(startdate, 'yyyy') = '0004'

Thanks

 

[hoinz]

Hi,
I guess startdate is in date format? not varchar?

...
set startdate = 
to_date('2004' || to_char(startdate, 'MMDDHH24MISS'), 'YYYYMMDDHH24MISS')
where ...

regards

 

[HimanB]

Hi,
Do as follows:

update labtrans_john
set startdate = to_date(replace(To_char(startdate,'DD-MON-YYYY HH24:MI:SS'), '0004', '2004'))
where to_char(startdate, 'yyyy') = '0004' 
/

Example:

SQL> select to_char(hiredate,'dd-mon-yyyy hh24:mi:ss') from emp where EMPNO=7899;

TO_CHAR(HIREDATE,'DD-MON-Y
--------------------------
17-sep-2004 19:01:59

SQL> ed
Wrote file afiedt.buf

  1  update emp set hiredate=to_date(replace(To_char(hiredate,'DD-MON-YYYY HH24:MI:SS'), '2004', '2006')) where
  2* to_char(hiredate,'YYYY')='2004'
  3  /

2 rows updated.

SQL> select to_char(hiredate,'dd-mon-yyyy hh24:mi:ss') from emp where EMPNO=7899;

TO_CHAR(HIREDATE,'DD-MON-Y
--------------------------
17-sep-2006 19:01:59

HTH
Regards
Himanshu

 

[Vandy02]

I keep getting this error:

--ORACLE-01830: date format picture ends before converting entire input string..

Note I am using TOAD

 

[SantaMufasa]

Vandy,

I'm certain we can help resolve your error; we just need to see your actual code and (if the data are coming from a table), we need to see your actual data.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:57 (17Sep04) UTC (aka "GMT" and "Zulu"), 11:57 (17Sep04) Mountain Time)