SQL: Get records created in time range for specific dates

sql oracle date-arithmetic
Mouna Cheikhna picture Mouna Cheikhna · Mar 5, 2012 · Viewed 48.7k times · Source

I have a set of records that were created last week, and from those I want to retrieve only those that were created between 6h45 and 19h15. I have a column creation_date that I can use.

How can I do this in sql?

Answer

APC picture APC · Mar 5, 2012

In Oracle we can turn dates into numbers and apply arithmetic to them in a variety of ways.

For instance sysdate-7 gives us the date seven days ago. trunc(some_date) removes the time element from a date column. And to_char(some_date, 'SSSSS') gives us its time element as the number of seconds since midnight. So 06:45:00 is 24300 seconds and 18:15:59 is 69359 seconds (please check those numbers, as they are back-of-an-envelope figgerin').

Anyway, putting that all together in a single query like this ...

select *
from your_table
where creation_date >= trunc(sysdate)-7
and to_number(to_char(creation_date, 'sssss')) between 24300 and 69359

... wil produce all the records created in the last week with a time element within core hours.

Related questions

Oracle DateTime in Where Clause?

I have sql something like this: SELECT EMP_NAME, DEPT FROM EMPLOYEE WHERE TIME_CREATED >= TO_DATE('26/JAN/2011','dd/mon/yyyy') -> This returns 10 rows and TIME_CREATED = '26-JAN-2011' Now when i do this …

Read More
Oracle date "Between" Query

I am using oracle database. i want to execute one query to check the data between two dates. NAME START_DATE ------------- ------------- Small Widget 15-JAN-10 04.25.32.000000 PM Product 1 17-JAN-10 04.31.32.000000 PM select * from <TABLENAME> where start_date BETWEEN '15…

Read More
Oracle SQL Where clause to find date records older than 30 days

I want to find records in a (Oracle SQL) table using the creation date field where records are older than 30 days. It would be nice to find records using a operators like > but if anyone can suggest quick SQL …

Read More