OraFAQ Forum: SQL & PL/SQL » How to truncate the minutes and seconds from a date field

Home » SQL & PL/SQL » SQL & PL/SQL » How to truncate the minutes and seconds from a date field

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to truncate the minutes and seconds from a date field [message #245990]

Tue, 19 June 2007 09:03

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?

Report message to a moderator

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

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

With "trunc".

Example that truncates to Hour :

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

Report message to a moderator

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

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>

Report message to a moderator

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

BlackSwan
Messages: 26766
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

Littlefoot
Messages: 21825
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>

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Aug 07 08:23:27 CDT 2025