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:
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.
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?
Absolutely - that is what a sane person would do.
Post a Comment