Oracle: Two useful date functions for getting start/end of day for a given date

Posted by Adrian Sat, 27 Jun 2009 21:05:00 GMT

Use these functions in Oracle when you want to remove the time portion of a date value. This is useful if you want to select values for a complete day, regardless of the time, for example.

Great Book

Here are two useful functions for processing dates. They take a valid date, e.g. “01/05/2007 10:22”, and either set the time to midnight (the start of the day) or 23:59 (the end of the day).

This allows us to use the dates to calculate to the whole day, rather than to a fraction, and to perform basic date management in cases where the time is not an important factor.

The functions:

function Date_ToStartOfDay (d date) return date
as
        return_value date;
begin

        return_value := to_date(to_char(d, 'DD-MM-YYYY'), 'DD-MM-YYYY');
        return return_value;

end;

function Date_ToEndOfDay (d date) return date
as
        return_value date;
begin

        return_value := to_date(to_char(d, 'DD-MM-YYYY') || ' 23:59', 'DD-MM-YYYY HH24:MI');
  return return_value;

end;

About

We are a small British company that produces business-oriented software and solutions. These articles are a product of our daily work - information that we think might be useful to share. We hope you find them useful.

Our Software

These are some of our products. Several are open source, some are web-based and others are proprietary:

Syndicate

Articles

Component Workshop Ltd