| Timestamp correction [message #177445] |
Wed, 14 June 2006 12:11  |
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  |
Frank
Messages: 7901
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 |
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 |
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 #311277 is a reply to message #177552] |
Thu, 03 April 2008 21:58 |
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 #311282 is a reply to message #177445] |
Thu, 03 April 2008 22:44 |
flyboy
Messages: 1903
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
