CODEWITHSUNDEEP
sqloracle-database
Jonathan Morningstar
Jonathan Morningstar

Reputation: 431

SYSDATE but specify the time

I want to say the follow but substitute the date with SYSDATE but the time between is what I want to adjust. What would the syntax be?

where mydatefield between SYSDATE+'0001' and SYSDATE+'2359'

...
WHERE TO_CHAR( MOPACTIVITY.MOPNOTIFICATIONSENDAT , 'yyyy-mm-dd hh24:mi' )
  BETWEEN '2013-07-26 00:00:01' AND '2013-07-26 23:59:59'
;

Upvotes: 3

Views: 19090

Answers (3)

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

SYSDATE (or any other date column) in Oracle has the time component. So you need to strip that off and then add the hours/minutes/time condition.

Eg. to say current day 10:00 AM to 3:00 PM, you can say

date_column between (trunc(sysdate) + 10/24) and (trunc(sysdate) + 15/24)

Oracle date arithmetic works on the day level. so, +1 will give you the next day, 1/24 will give you an hour and 10/24 will give you 10:00 AM in the current day.

SQL> alter session set nls_date_format = 'DD-Mon-YYYY HH:MI:SS AM';

Session altered.

  1  select sysdate,
  2         trunc(sysdate),
  3         trunc(sysdate) + 10/24,
  4         trunc(sysdate) + 15/24
  5* from dual
SQL> /

SYSDATE                  26-Jul-2013 06:26:07 PM
TRUNC(SYSDATE)           26-Jul-2013 12:00:00 AM 
TRUNC(SYSDATE)+10/24     26-Jul-2013 10:00:00 AM 
TRUNC(SYSDATE)+15/24     26-Jul-2013 03:00:00 PM

For your question, you seem to be interested between current day and next day, so you can try adding + 1 to the date directly, once you strip the time component.

date_column >= trunc(sysdate) and 
date_column < trunc(sysdate)+1

Upvotes: 8

Jasti
Jasti

Reputation: 947

We can also trunc both the dates and then compare the result

where TRUNC(MOPACTIVITY.MOPNOTIFICATIONSENDAT) = TRUNC(SYSDATE)

TRUNC Removes the timestamp from the dates

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26343

The best way to do this is to leave your MOPACTIVITY.MOPNOTIFICATIONSENDAT as a DATE type. That allows Oracle to optimize the query if there happens to be an index on the column. I'd recommend something like this:

WHERE MOPACTIVITY.MOPNOTIFICATIONSENDAT >= TRUNC(SYSDATE)
  AND MOPACTIVITY.MOPNOTIFICATIONSENDAT < TRUNC(SYSDATE) + 1

That boils down to "greater than or equal to today at midnight" and "less than tomorrow at midnight".

Upvotes: 1

Related Questions