Home » SQL & PL/SQL » SQL & PL/SQL » Previous day (Oracle, 8.1.7.0.0, Windows Server 2007)
Previous day [message #580007] Tue, 19 March 2013 02:48 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi Experts,

What command can be used to take the previous day upto mid night?

For example

TO_DATE('18-MAR-13 23:59:59', 'dd-mon-yy hh24:mi:ss')

Instead of me entering the date any way to take the previous day till mid-night.

I dont think sysdate-1 will help me because if I enter sysdate-1 it will take from now -1 that means 18-mar-13 15.45.45 but I want till the previous date until mid-night.

Can anyone help?
Re: Previous day [message #580008 is a reply to message #580007] Tue, 19 March 2013 03:01 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
You can use SYSDATE - 1 with TRUNC function.

regards,
Delna
Re: Previous day [message #580011 is a reply to message #580007] Tue, 19 March 2013 03:23 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / France
Senior Member
kumarvk wrote on Tue, 19 March 2013 08:48
I dont think sysdate-1 will help me because if I enter sysdate-1 it will take from now -1 that means 18-mar-13 15.45.45 but I want till the previous date until mid-night ...


As Delna said, TRUNC with SYSDATE - 1 does the job.

This is also explained in the online documentation:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions220.htm#SQLRF06151
Quote:

. . .
The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. This function is not sensitive to the NLS_CALENDAR session parameter. It operates according to the rules of the Gregorian calendar. The value returned is always of data type DATE, even if you specify a different datetime data type for date. If you omit fmt, then the default format model 'DD' is used and the value returned is date truncated to the day with a time of midnight. Refer to "ROUND and TRUNC Date Functions" for the permitted format models to use in fmt.
. . .



Regards,
Dariyoosh
Re: Previous day [message #580025 is a reply to message #580007] Tue, 19 March 2013 07:55 Go to previous message
joy_division
Messages: 4559
Registered: February 2005
Location: East Coast USA
Senior Member
kumarvk wrote on Tue, 19 March 2013 03:48
Hi Experts,

What command can be used to take the previous day upto mid night?

For example

TO_DATE('18-MAR-13 23:59:59', 'dd-mon-yy hh24:mi:ss')


trunc(sysdate)-1/86400
Previous Topic: GROUPING ISSUE (merged)
Next Topic: Bulk Collect
Goto Forum:
  


Current Time: Thu Dec 18 19:44:11 CST 2014

Total time taken to generate the page: 0.11275 seconds