I came across a great link for handling dates in Oracle by Philip Greenspun.


Specifically this helps when fetching results by a given date range based on today’s date.

Here is a hilight of this article in case it is no longer active:

You can query for shorter time intervals:

select email, registration_date
from users
where registration_date > current_date - interval '1' hour;
EMAIL                               REGISTRATION_DATE
----------------------------------- -------------------------
former-president@whitehouse.gov     2003-06-13 15:18:22

This becomes very powerful when you want to use any time interval (ie, day, month etc)

This section illustrates how you can format the dates as well:

select email, to_char(registration_date,'Day, Month DD, YYYY') as reg_day
from users
order by registration_date;
EMAIL                               REG_DAY
----------------------------------- -----------------------------
schlomo@mendelowitz.com             Friday   , June      13, 2003