Home » SQL & PL/SQL » SQL & PL/SQL » TRUNC(SYSDATE,'mm')-1 and appending a time
TRUNC(SYSDATE,'mm')-1 and appending a time [message #244440] Tue, 12 June 2007 16:50 Go to next message
welchaz
Messages: 23
Registered: April 2005
Location: Tucson, AZ
Junior Member
I'm trying to create a query to select an ending date/time from the previous month as the end range date for selecting records. I have been using "TRUNC(SYSDATE,'mm')-1" which works well for getting the last date of the previous month, but it doesn't include the time portion of the date. I need the time portion to be "23:59:59"...so for example, if I ran the query today (6/12/07), I need the result "31-MAY-2007 23:59:59". I have tried several things, but nothing is working so far.

Any help is appreciated.

Re: TRUNC(SYSDATE,'mm')-1 and appending a time [message #244441 is a reply to message #244440] Tue, 12 June 2007 17:02 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
You need to be smarter than the equipment your are working with.
  1* select to_char(trunc(sysdate,'mm')-1,'YYYY-MM-DD ')||'23:59:59' LAST_MONTH from dual
SQL> /

LAST_MONTH
-------------------
2007-05-31 23:59:59
Re: TRUNC(SYSDATE,'mm')-1 and appending a time [message #244442 is a reply to message #244441] Tue, 12 June 2007 17:34 Go to previous messageGo to next message
welchaz
Messages: 23
Registered: April 2005
Location: Tucson, AZ
Junior Member
Thank you.
Re: TRUNC(SYSDATE,'mm')-1 and appending a time [message #244444 is a reply to message #244440] Tue, 12 June 2007 18:32 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
1* select sysdate,trunc(sysdate,'mm')-(1/24/60/60) eom_w_time
SQL> /

SYSDATE EOM_W_TIME
-------------------- --------------------
12-jun-2007 19:31:52 31-may-2007 23:59:59

1 row selected.

SQL>

just subtract one second.
Previous Topic: Multiple Insert, Internal Query and Static Value
Next Topic: bull collect loop syntax
Goto Forum:
  


Current Time: Mon Dec 05 06:47:15 CST 2016

Total time taken to generate the page: 0.05198 seconds