Home » SQL & PL/SQL » SQL & PL/SQL » working with dates help (Oracle SQL)
working with dates help [message #359390] Sat, 15 November 2008 11:37 Go to next message
lacoste
Messages: 4
Registered: November 2008
Junior Member
Hello

I'm trying to change the hour in table containing different times to a specific time.
For example,

15-JAN-2008 14:23:32 to 15-JAN-2008 23:59:59

using SQLPlus

How can I do this??

Thanks a lot!
Re: working with dates help [message #359394 is a reply to message #359390] Sat, 15 November 2008 12:06 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
UPDATE timetab SET timecol = TO_DATE('15-JAN-2008 23:59:59', DD-MON-YYYY HH24:MI:SS');
Re: working with dates help [message #359403 is a reply to message #359390] Sat, 15 November 2008 12:47 Go to previous messageGo to next message
lacoste
Messages: 4
Registered: November 2008
Junior Member
thanks for answering

The actual problem is that I have a table with different dates and times, and what I want is keep the date and change the time to 23:59:59.

12-JAN-2008 14:23:14 to 12-JAN-2008 23:59:59
15-AUG-2008 11:26:56 to 15-AUG-2008 23:59:59
16-OCT-2008 23:53:50 to 16-OCT-2008 23:59:59

and it's a very long table (6000 lines)

Thanks a lot!

Re: working with dates help [message #359409 is a reply to message #359403] Sat, 15 November 2008 13:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
trunc(date) gives you the date at 0:00:00.
+1 gives you next day.
1/86400 is one second.

Regards
Michel
Re: working with dates help [message #359410 is a reply to message #359403] Sat, 15 November 2008 13:26 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
I cannot test right now, but something like this should work:

UPDATE timetab SET timecol =
TO_DATE(TO_CHAR(timecol, 'DD-MON-YYYY')||' 23:59:59', DD-MON-YYYY HH24:MI:SS'));

Re: working with dates help [message #359414 is a reply to message #359409] Sat, 15 November 2008 13:44 Go to previous messageGo to next message
lacoste
Messages: 4
Registered: November 2008
Junior Member
Thanks a lot!!! worked properly!!!

Re: working with dates help [message #359419 is a reply to message #359414] Sat, 15 November 2008 14:15 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Another option (based on Michel's hint) is a little bit simpler:
SQL> select col, trunc(col) + 86399/86400 new_time from test;

COL                 NEW_TIME
------------------- -------------------
12.01.2008 14:23:14 12.01.2008 23:59:59
15.08.2008 11:26:56 15.08.2008 23:59:59
16.10.2008 23:53:50 16.10.2008 23:59:59
Re: working with dates help [message #359420 is a reply to message #359419] Sat, 15 November 2008 15:08 Go to previous message
lacoste
Messages: 4
Registered: November 2008
Junior Member
Thanks Littlefoot I'm gonna try with this option.

Best Regards.
Previous Topic: how can one construct lexical analyzer in pl sql?
Next Topic: convert GMT TO day light saving time
Goto Forum:
  


Current Time: Mon Dec 05 08:28:27 CST 2016

Total time taken to generate the page: 0.06559 seconds