FirebirdSQL: Create Range of dates, fraction in days, hours, minutes, months or years

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.