Oracle: Two useful date functions
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;