Oracle: Two useful date functions

adrian@arctus.co.uk (Adrian O'Connor) — Tue, 01 May 2007 05:21:00 -0400

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;

Arctus Blog: Oracle: Two useful date functions

Oracle: Two useful date functions