OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » date function

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

date function [message #619222]

Sun, 20 July 2014 08:31

hahaie
Messages: 194
Registered: May 2014

Senior Member

hello,
Is there function for the following operations?
1.for example:
in:01/10/2014 10:25:14 ====>out:01/10/2014 00:00:00
or
in:13/01/2015 13:15:05 ====>out:13/01/2015
--------------------------------------------------------
2.for example:
in:14/12/2014 03:25:12 ====>out:14/12/2014 23:59:59
or
in:28/11/2014 12:10:05 ====>out:28/11/2014 23:59:59
--------------------------------------------------------
The output of the above
Date format should be.
tanks.

Report message to a moderator

Re: date function [message #619224 is a reply to message #619222]

Sun, 20 July 2014 08:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

what is datatype of input argument?
what is datatype of function output?

Report message to a moderator

Re: date function [message #619226 is a reply to message #619224]

Sun, 20 July 2014 08:53

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate,
  2         trunc(sysdate) result_1,
  3         trunc(sysdate + 1) - 1 / (24 * 60 * 60) result_2
  4  from dual;

SYSDATE             RESULT_1            RESULT_2
------------------- ------------------- -------------------
20.07.2014 15:52:59 20.07.2014 00:00:00 20.07.2014 23:59:59

SQL>

Report message to a moderator

Re: date function [message #619229 is a reply to message #619222]

Sun, 20 July 2014 09:46

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> select sysdate,
  2         to_char(trunc(sysdate),'DD/MM/YYYY HH24:MI:SS') result_1a,
  3         to_char(sysdate,'DD/MM/YYYY "00:00:00"') result_1b,
  4         to_char(sysdate,'DD/MM/YYYY') result_2,
  5         to_char(trunc(sysdate+1)-1/(24*60*60),'DD/MM/YYYY HH24:MI:SS') result_3a,
  6         to_char(sysdate,'DD/MM/YYYY "23:59:59"') result_3b
  7  from dual
  8  /
SYSDATE             RESULT_1A           RESULT_1B           RESULT_2   RESULT_3A           RESULT_3B
------------------- ------------------- ------------------- ---------- ------------------- -------------------
20/07/2014 16:47:23 20/07/2014 00:00:00 20/07/2014 00:00:00 20/07/2014 20/07/2014 23:59:59 20/07/2014 23:59:59

[Updated on: Sun, 20 July 2014 09:47]

Report message to a moderator