Home » SQL & PL/SQL » SQL & PL/SQL » Timestamp correction
Timestamp correction [message #177445] Wed, 14 June 2006 12:11 Go to next message
abollinger123
Messages: 14
Registered: January 2006
Location: NC
Junior Member
My client has presented me with an issue. Seems they have loaded thousands of project records via an xml loading tool. However when they created the files (using Pearl scripts) they didn't set the project finish date fields with a valid time stamp. The end result is that these date fields now show a value of:

09/30/06 12:00:00
11/30/06 12:00:00, etc.

I need a script or something to update all these thousands of table records (date field) whereby preserving the date but just updating all the time stamps to 17:00:00 ...(5pm in the afternoon).

Sample table code...
CREATE TABLE Z_PROJECT_TEST ( 
    PRID    	NUMBER(22,0) NOT NULL,
    PRFINISH	DATE NOT NULL 
    )

Sample table data...
INSERT INTO NIKU.Z_PROJECT_TEST(PRID, PRFINISH) 
    VALUES(5000264, to_date('09/30/06 12:00:00', 'MM/DD/YY HH:MI:SS'));
    VALUES(5000265, to_date('11/30/06 12:00:00', 'MM/DD/YY HH:MI:SS'));
    VALUES(5000266, to_date('12/31/06 12:00:00', 'MM/DD/YY HH:MI:SS'));

Does anyone know if this can be done without altering the date component values?

Thanks again.
Allen
Re: Timestamp correction [message #177447 is a reply to message #177445] Wed, 14 June 2006 12:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
add 5 * 1/24 to it (1/24 being 1 hour)
or
set new date to
to_date(to_char(prfinish, 'mm/dd/yyyy')||' 17:00'), 'mm/dd/yyyy hh24:mi')

This way, you take the date-part of prfinish and glue 17.00 to it.

hth
Re: Timestamp correction [message #177448 is a reply to message #177447] Wed, 14 June 2006 12:59 Go to previous messageGo to next message
abollinger123
Messages: 14
Registered: January 2006
Location: NC
Junior Member
I'm trying your suggestion as follows:

update Z_PROJECT_TEST set PRFINISH = (to_date(to_char(prfinish,'MM/DD/YYYY')||'17:00'),'MM/DD/YYYY HH24:MI')


Keeps on complaining about missing a right parenthesis...I assumed from your example this would be an update script on this table to correct the existing dates..
Re: Timestamp correction [message #177449 is a reply to message #177447] Wed, 14 June 2006 13:07 Go to previous messageGo to next message
abollinger123
Messages: 14
Registered: January 2006
Location: NC
Junior Member
Got it!...just a matter of () in the right order..

update Z_PROJECT_TEST set PRFINISH = to_date(to_char(prfinish,'MM/DD/YYYY')||'17:00','MM/DD/YYYY HH24:MI')


Thanks very much for the suggestion...another item for the knowledge garden which is growing in my head...
Re: Timestamp correction [message #177484 is a reply to message #177449] Wed, 14 June 2006 23:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ah, sorry for the parethesis-error. Did it of the top of my head.
Re: Timestamp correction [message #177552 is a reply to message #177484] Thu, 15 June 2006 08:02 Go to previous messageGo to next message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

Make it simple!

UPDATE z_project_test
   SET prfinish = TRUNC (prfinish) + 17 / 24



[Updated on: Fri, 04 April 2008 00:40] by Moderator

Report message to a moderator

Re: Timestamp correction [message #177572 is a reply to message #177552] Thu, 15 June 2006 09:21 Go to previous messageGo to next message
abollinger123
Messages: 14
Registered: January 2006
Location: NC
Junior Member
Wow!.. Another great and simple answer that works great. Thanks again for the post.
Re: Timestamp correction [message #311277 is a reply to message #177552] Thu, 03 April 2008 21:58 Go to previous messageGo to next message
aichoora
Messages: 1
Registered: April 2008
Junior Member
there is some danger..

see the result value of:

select TRUNC (sysdate + 13/24 ) + 17/24 from dual

select TRUNC (sysdate + 11/24 ) + 17/24 from dual

the date value is changed by time !!
Re: Timestamp correction [message #311279 is a reply to message #177445] Thu, 03 April 2008 22:20 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>there is some danger..
HUH?

>the date value is changed by time !!
HUH?

SQL> select TRUNC (sysdate + 13/24 ) + 17/24 from dual;

TRUNC(SYSDATE+1
---------------
04-APR-08

SQL> select TRUNC (sysdate + 11/24 ) + 17/24 from dual;

TRUNC(SYSDATE+1
---------------
04-APR-08
Re: Timestamp correction [message #311282 is a reply to message #177445] Thu, 03 April 2008 22:44 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> there is some danger..

Yes, when you do not know or understand, what TRUNC and/or date arithmetics does.
Maybe you was pointing to the fact, that adding some hours to date with time portion may result in the next day.
But I see no reason for doing that, nor it was included in zozogirl's solution.
Previous Topic: ORA-00933 when trying out returning clause
Next Topic: Regarding Collections
Goto Forum:
  


Current Time: Sun Dec 11 00:45:19 CST 2016

Total time taken to generate the page: 0.04937 seconds