CREATE OR ALTER PROCEDURE date_range(
startdate date,
enddate date,
interv integer DEFAULT 1,
unit varchar(6) DEFAULT 'DAY',
fromtime time DEFAULT null,
totime time DEFAULT null
) RETURNS (dateval timestamp)
AS
BEGIN
dateval = startdate;
while (dateval < enddate) do
BEGIN
IF ((:fromtime IS NULL OR cast(:dateval AS time) >= :fromtime)
AND (:totime IS NULL OR cast(:dateval AS time) < :totime)) THEN
BEGIN
suspend;
END
SELECT
CASE
WHEN :unit = 'MINUTE' THEN dateadd(MINUTE, :interv, :dateval)
WHEN :unit = 'HOUR' THEN dateadd(HOUR, :interv, :dateval)
WHEN :unit = 'YEAR' THEN dateadd(YEAR, :interv, :dateval)
WHEN :unit = 'MONTH' THEN dateadd(MONTH, :interv, :dateval)
else dateadd(DAY, :interv, :dateval)
END
FROM RDB$DATABASE
INTO :dateval;
END
END;
/*
Example: ranges from today to next 10 days, from 8:00am to 6:00pm, in fraction of 30 minutes:
SELECT
cast(dateval AS date) TheDate,
CAST(dateval AS time) FromTime,
CAST(dateadd(MINUTE, 30, dateval) AS time) ToTime
FROM date_range(
current_date, ---- initial date
current_date + 10, ---- next 10 days
30, 'MINUTE', ---- fraction
time '08:00', time '18:00') --- from time, to time
*/
Dates and times between two dates, using fraction in years, months, days, hours or minutes.
Be the first to comment
You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.