Thursday, March 09, 2006

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;

3 comments:

William Robertson said...

Tee hee, that leap year check is going to fail in 2100.

Who needs constants, when you can just repeat each SUBSTR expression five times? It reminded me of the excellent Joel Spolsky article Back to Basics (rather a lot to read online, but I highly recommend the book) about how string manipulation has to work internally.

matheus said...

What if you wrap the To_Date() function inside a PL/SQL block? If it goes right, return true; if an exception is raised, return false?

William Robertson said...

Absolutely - that is what a sane person would do.