Home » SQL & PL/SQL » SQL & PL/SQL » How to truncate the minutes and seconds from a date field
How to truncate the minutes and seconds from a date field [message #245990] Tue, 19 June 2007 09:03 Go to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
I have got a date field which is set to 13/07/2007 13:43:55. I want to format this field to 13/07/2007 13:00:00. How can I do this?
Re: How to truncate the minutes and seconds from a date field [message #245991 is a reply to message #245990] Tue, 19 June 2007 09:08 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
With "trunc".

Example that truncates to Hour :

SELECT Trunc(SYSDATE,'HH') FROM dual;
Re: How to truncate the minutes and seconds from a date field [message #246068 is a reply to message #245991] Tue, 19 June 2007 14:46 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
did you check Before posting

SQL> select sysdate from dual;

SYSDATE
---------
19-JUN-07

SQL>  SELECT Trunc(SYSDATE,'HH') FROM dual;

TRUNC(SYS
---------
19-JUN-07

SQL>
Re: How to truncate the minutes and seconds from a date field [message #246069 is a reply to message #245990] Tue, 19 June 2007 14:51 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Or depending upon NLS default value:
SQL> SELECT Trunc(SYSDATE,'HH') FROM dual;

TRUNC(SYSDATE,'HH')
-------------------
2007-06-19 12:00:00



perhaps you meant
SQL> select to_char(sysdate,'HH') from dual;

TO
--
12

[Updated on: Tue, 19 June 2007 14:53] by Moderator

Report message to a moderator

Re: How to truncate the minutes and seconds from a date field [message #246134 is a reply to message #246068] Wed, 20 June 2007 01:28 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
DreamzZ wrote on Tue, 19 June 2007 21:46
did you check Before posting

SQL> select sysdate from dual;

SYSDATE
---------
19-JUN-07

SQL>  SELECT Trunc(SYSDATE,'HH') FROM dual;

TRUNC(SYS
---------
19-JUN-07

SQL>


LOL! That's a good one! If your default NLS_DATE_FORMAT settings are such that you don't see the difference, it doesn't mean that NOBODY sees the difference.
SQL> alter session set nls_date_Format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
20.06.2007 08:27:01

SQL> select trunc(sysdate, 'hh') from dual;

TRUNC(SYSDATE,'HH')
-------------------
20.06.2007 08:00:00

SQL>
Previous Topic: how to directly pass xml value to clob variable
Next Topic: PLS-00306: wrong number or types of arguments
Goto Forum:
  


Current Time: Sat Dec 10 14:46:05 CST 2016

Total time taken to generate the page: 0.06452 seconds