Home » SQL & PL/SQL » SQL & PL/SQL » Please provide function for going at start and end time for the day from sysdate (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Please provide function for going at start and end time for the day from sysdate [message #576553] Wed, 06 February 2013 04:11 Go to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Dear Experts,

I need to go on day start and end time for each day.Please provide if there is having any function that will go on start and end time according to sysdate for that day.
Re: Please provide function for going at start and end time for the day from sysdate [message #576558 is a reply to message #576553] Wed, 06 February 2013 04:21 Go to previous messageGo to next message
Littlefoot
Messages: 19537
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It's probably me, but - I don't understand what you ask. Function returns something. What is this function supposed to return (and, possibly, accept as an input parameter(s))?
Re: Please provide function for going at start and end time for the day from sysdate [message #576559 is a reply to message #576558] Wed, 06 February 2013 04:24 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Dear Little ffot,

As per report i need to retrieve data for single day and input should be consider from sysdate.Like data should come between
to_date(07/02/2013 00:00:00','DD/MM/YYYY HH24:MI:SS') and to_date(07/02/2013 23:59:59','DD/MM/YYYY HH24:MI:SS')

but i can access starting time using


select to_char(trunc(sysdate),'DD-MM-YYYY HH24:MI:SS')
from   dual;

[Updated on: Wed, 06 February 2013 04:26]

Report message to a moderator

Re: Please provide function for going at start and end time for the day from sysdate [message #576560 is a reply to message #576559] Wed, 06 February 2013 04:35 Go to previous messageGo to next message
Littlefoot
Messages: 19537
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
For END value:
- trunc(sysdate)
- add 1 to it (so it'll become "tomorrow at 00:00:00")
- subtract one second (1 / (24 hours * 60 minutes * 60 seconds))
SQL> select trunc(sysdate) c_start,
  2         (trunc(sysdate) + 1) - 1/(24*60*60) c_end
  3  from dual;

C_START             C_END
------------------- -------------------
06.02.2013 00:00:00 06.02.2013 23:59:59

SQL>
Re: Please provide function for going at start and end time for the day from sysdate [message #576562 is a reply to message #576559] Wed, 06 February 2013 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
between trunc(sysdate) and trunc(sysdate)+1-1/86400

Regards
Michel
Re: Please provide function for going at start and end time for the day from sysdate [message #576568 is a reply to message #576562] Wed, 06 February 2013 04:44 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Many thanks Michel and Littlefoot
Re: Please provide function for going at start and end time for the day from sysdate [message #576593 is a reply to message #576568] Wed, 06 February 2013 07:19 Go to previous messageGo to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

Hi All,

What will be the problem If I am using like below for checking between today's start and today's end.Please suggest.
WITH TEMP AS
  (SELECT TO_DATE('06-FEB-2013 23:59:59','DD-MON-YYYY HH24:MI:SS') SOMEDATE
  FROM DUAL
  )
SELECT somedate
FROM temp
WHERE somedate >=TRUNC(SYSDATE)
AND somedate    <TRUNC(SYSDATE)+1;


Regards,
Nathan
Re: Please provide function for going at start and end time for the day from sysdate [message #576594 is a reply to message #576593] Wed, 06 February 2013 07:25 Go to previous message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
No problem, it works the same as Michel and Littlefoots examples.
Previous Topic: DBMS_OUTPUT Error
Next Topic: Another Pivot question
Goto Forum:
  


Current Time: Tue Sep 02 02:29:44 CDT 2014

Total time taken to generate the page: 0.10633 seconds