February PL/SQL Puzzler!

Visit the PL/SQL Pipeline - a free internet forum with lively discussion groups, technical White Papers, monthly Diary and the "PL/SQL Puzzler". Solve it and your name will be enteresd into a drawing for your choice of a free technical book from O'Reilly & Associates. The PL/SQL Pipeline is hosted by world renowned author and instructor Steven Feuerstein, and sponsored by RevealNet. Stop by and give it a try! http://www.revealnet.com/plsql-pipeline/index.htm

FEBRUARY'S PL/SQL PUZZLER, by Steven Feuerstein

I hate dumb software. What's dumb software? Programs that make you do things their way, as opposed to being flexible enough to meet your needs and varied approaches.

Want a classic example of dumb software? The TO_DATE function of SQL and PL/SQL. Consider, for a moment, the way that Oracle allows you to work with dates.

The Oracle Server offers the ability to set a default date format for each instance of a database with the NLS_DATE_FORMAT initialization parameter. Oracle provides a ruthlessly efficient gatekeeper for its RDBMS: there is no way you will ever be able to enter an invalid date into the database. And there are lots of functions that enable you to perform arithmetic on dates once they are in the database.

That's the good part. There are, unfortunately, obstacles to working with dates, especially when you need to convert a string to a date:

•Oracle tools do not make it easy for users to enter dates. Oracle Forms, for example, insists that when a user enters a date, that entry must conform to a single date format mask—either the system-wide default, or an override for that particular item.
•Users cannot enter partial date information and have the rest defaulted by the system. Shouldn't users be able to enter a date in any manner they please? That same date mask demands that you enter every part of the date, even if it is always going to be in the current month or year.

It seems to me that users should be able to enter the smallest number of characters needed to specify their date, and let the application figure out the rest.

And then, of course, there is the dreaded Year 2000 problem in Oracle. If your code contains lines like this:

v_mydate := TO_DATE (v_mystr, 'MM/DD/YY');

then you have some big problems coming down the pike.

Here is my puzzle for February 1998:

Develop an alternative to TO_DATE which allows a user to pass in just about any string containing date information in any format and get a valid date in return....

• There's much more to this puzzle on the PL/SQL Pipeline. Use the pipeline to get full information, hints, etc. You must also submit your answer on the PL/SQL Pipeline.**