Re: How to search between dates

From: <maaher_at_my-deja.com>
Date: 2000/06/29
Message-ID: <8jfc17$2hh$1_at_nnrp1.deja.com>#1/1

Hi Arun,
This is a procedure that could solve your question:

CREATE OR REPLACE PROCEDURE SHOW_AVAILABILITY     ( p_equipname IN <table_name>.EquipName%TYPE) IS
 prev_end <table_name>.EndDate%TYPE := NULL; BEGIN
-- The second where condition limits the resultset.

• I assume you don't want to make reservations in the past... FOR c1 IN ( SELECT StartDate,EndDate FROM <table_name> WHERE EquipName = p_equipname AND StartDate > (SYSDATE-1) ORDER BY StartDate) LOOP IF prev_end IS NOT NULL THEN dbms_output.put_line('The equipment '||p_equipname|| ' is free from '||prev_end|| ' to '||c1.StartDate); END IF; dbms_output.put_line('Equipment '||p_equipname|| ' is occupied from '|| c1.StartDate||' to '||c1.EndDate); prev_end := c1.EndDate; END LOOP; dbms_output.put_line('Equipment '||p_equipname|| ' is free from '||prev_end); EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLCODE||': '||SQLERRM); END;

I hope this helps you out. This is written in Notepad (and during lunch break), so it is entirely possible that it contains syntax or other errors, but it should give you a start.

Regards,
Maarten

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jun 29 2000 - 00:00:00 CEST