CODEWITHSUNDEEP
sqloracle-databasedatetimeoracle11gdate-arithmetic
kumarb
kumarb

Reputation: 535

Oracle DB select between dates

I would like to query number of records between night 12.01 AM to 11.59 PM but issue is, I would like to schedule this query so I cant specify any hard coded dates.

Query should pull number of records for query date between 12.01 AM to 11.59 PM. Could someone please help me on this.

Upvotes: 0

Views: 501

Answers (2)

TommCatt
TommCatt

Reputation: 5636

I'm going to assume you want everything that happens during the date of interest. So you want everything from and including midnight of that day and before midnight of the next day.

declare
    AsOf Date = date '2015-01-01 13:14:15';

select  ...
from    tablename
where   tabledate >= trunc( AsOf )
    and tabledate < trunc( AsOf ) + 1;

If you know the date doesn't have a time portion, just can eliminate the calls to trunc. But you may want to keep them just in case.

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

Query should pull number of records for query date between 12.01 AM to 11.59 PM.

You could do it as:

  • TRUNC gives you date element truncating the time portion
  • convert the SYSDATE into string using TO_CHAR
  • then concatenate the time element
  • finally convert everything back to DATE

SYSDATE returns the current date and time set for the operating system on which the database resides. The datatype of the returned value is DATE, and the format returned depends on the value of the NLS_DATE_FORMAT initialization parameter.

So, you don't have to hard-code any DATE value if you want to execute the query everyday.

Use the following in the filter predicate:

BETWEEN 
        TO_DATE(TO_CHAR(TRUNC(SYSDATE), 'MM/DD/YYYY') ||' 00:01', 'MM/DD/YYYY HH24:MI') 
AND     
        TO_DATE(TO_CHAR(TRUNC(SYSDATE), 'MM/DD/YYYY') ||' 23:59', 'MM/DD/YYYY HH24:MI')

Demo

SQL> alter session set nls_date_format = 'MM/DD/YYYY HH24:MI:SS';

Session altered.

SQL> SELECT to_date(TO_CHAR(TRUNC(SYSDATE), 'MM/DD/YYYY')
  2    ||' 00:01', 'MM/DD/YYYY HH24:MI') start_dt ,
  3    to_date(TO_CHAR(TRUNC(SYSDATE), 'MM/DD/YYYY')
  4    ||' 23:59', 'MM/DD/YYYY HH24:MI') end_date
  5  FROM dual;

START_DT            END_DATE
------------------- -------------------
05/06/2015 00:01:00 05/06/2015 23:59:00

SQL>

So, you don't have to put any hard-coded value for current date, the SYSDATE will take care of it. All you are doing is:

  • TRUNC gives the date element by truncating the time portion.
  • Then concatenating the required time element
  • Converting the entire string into DATE using TO_DATE

I would like to schedule this query so I cant specify any hardcord dates

To schedule the query to execute everyday, you could use DBMS_SCHEDULER.

Upvotes: 1

Related Questions