What is it with dates?

What exactly is it with dates that so many Oracle developers struggle with? Why do they go to such lengths to avoid using the DATE type? Why, if DATE validation or arithmetic is required, would they use CHARs or NUMBERs? Abuses of DATEs seems to be a recurring theme on Oracle WTF. Indeed, here's another good example kindly provided by Graham Oakes.

Over to Graham...

---

This is a cracker, the easy way to check the supplied date in a string (after all who actually wants to use date types) is a valid date.

IF to_number(substr(v_valuedate,3,2)) NOT BETWEEN 1 AND 12
THEN
    v_rowstatustype := -190;
ELSE
    -- check 31 day months
    IF substr(v_valuedate,3,2) IN ('01','03','05','07','08','10','12')
    THEN
        IF to_number(substr(v_valuedate,1,2)) > 31
        THEN
            v_rowstatustype := -200;
        END IF;

    -- check 30 day months
    ELSIF substr(v_valuedate,3,2) IN ('04','06','09','11')
    THEN
        IF to_number(substr(v_valuedate,1,2)) > 30
        THEN
            v_rowstatustype := -200;
        END IF;

    -- check leap year feb
    ELSIF substr(v_valuedate,3,2) = '02'
          AND MOD(to_number(substr(v_valuedate,5,4)),4) = 0
    THEN
        IF to_number(substr(v_valuedate,1,2)) > 29
        THEN
            v_rowstatustype := -200;
        END IF;

    -- check non-leap year feb
    ELSIF substr(v_valuedate,3,2) = '02'
          AND MOD(to_number(substr(v_valuedate,5,4)),4) != 0
    THEN
        IF to_number(substr(v_valuedate,1,2)) > 28
        THEN
            v_rowstatustype := -200;
        END IF;
    END IF;
END IF;