Home » SQL & PL/SQL » SQL & PL/SQL » how to round the datetime field to the nearest hour?
how to round the datetime field to the nearest hour? [message #244020] Mon, 11 June 2007 05:53 Go to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
I have got two datetime fields and I want to round these fields to the nearest hour

Date 1

07/06/2007 09:53:14

I want to convert the above date to 07/06/2007 10:00:00

Date 2

07/06/2007 13:15:17

I want to convert the above date to 07/06/2007 13:00:00
Re: how to round the datetime field to the nearest hour? [message #244026 is a reply to message #244020] Mon, 11 June 2007 06:20 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ROUND does the trick:
SSQL> WITH yourtable AS
  2    (
  3      SELECT TO_DATE( '07/06/2007 09:53:14'
  4                    , 'DD/MM/YYYY HH24:MI:SS'
  5                    ) thedate
  6      FROM dual
  7      UNION ALL
  8      SELECT TO_DATE( '07/06/2007 13:15:17'
  9                    , 'DD/MM/YYYY HH24:MI:SS'
 10                    ) thedate
 11      FROM dual
 12    )
 13  SELECT ROUND(thedate,'HH24') thedate
 14  FROM   yourtable
 15  /

THEDATE
-------------------
07/06/2007 10:00:00
07/06/2007 13:00:00


MHE

[Updated on: Mon, 11 June 2007 06:21]

Report message to a moderator

Re: how to round the datetime field to the nearest hour? [message #244029 is a reply to message #244026] Mon, 11 June 2007 06:30 Go to previous message
haiza
Messages: 22
Registered: June 2007
Junior Member
Thanks, I figure that out when i post the request.
Previous Topic: SQL Loader / Temp table
Next Topic: Limitation of Oracle XE
Goto Forum:
  


Current Time: Sat Dec 10 18:58:48 CST 2016

Total time taken to generate the page: 0.08834 seconds