Mega Code Archive
Check date value range
<![CDATA[
SQL>
SQL> CREATE OR REPLACE FUNCTION date_range
2 (p_low_end_date DATE,
3 p_high_end_date DATE,
4 p_to_check_date DATE)
5 RETURN BOOLEAN IS
6
7 returnBoolean BOOLEAN := FALSE;
8 smallDate DATE := TRUNC(p_low_end_date); -- Time 00:00:00
9 largeDate DATE := TRUNC(p_high_end_date + 1) - .000011574;
10 BEGIN
11 IF p_to_check_date > smallDate AND
12 p_to_check_date <= largeDate THEN
13 returnBoolean := TRUE;
14 END IF;
15
16 DBMS_OUTPUT.PUT_LINE('Low Date : ' || TO_CHAR(smallDate, 'MM/DD/YYYY:HH24:MI:SS'));
17 DBMS_OUTPUT.PUT_LINE('Date To Check: ' || TO_CHAR(p_to_check_date, 'MM/DD/YYYY:HH24:MI:SS'));
18 DBMS_OUTPUT.PUT_LINE('High Date : ' || TO_CHAR(largeDate, 'MM/DD/YYYY:HH24:MI:SS'));
19 RETURN returnBoolean;
20 END date_range;
21 /
Function created.
SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> DECLARE
2 checkBoolean BOOLEAN;
3 BEGIN
4 checkBoolean := date_range(SYSDATE, SYSDATE, SYSDATE);
5 IF checkBoolean THEN
6 DBMS_OUTPUT.PUT_LINE('Date in Range: TRUE');
7 ELSE
8 DBMS_OUTPUT.PUT_LINE('Date in Range: FALSE');
9 END IF;
10 END;
11 /
Low Date : 06/19/2008:00:00:00
Date To Check: 06/19/2008:18:55:03
High Date : 06/19/2008:23:59:59
Date in Range: TRUE
PL/SQL procedure successfully completed.
SQL>
]]>