Handling Y2K Date Input With Pseudo-IF

With all the discussion about Y2K, here is an example of how to handle Y2K date input by users into your custom sqlplus scripts, which defines an SQL variable, fieldval, in the form of dd-MON-yyyy, for a date entered either in the form of dd-MON-yy (such as 7-Apr-99), in which case the year is converted using the RR format (0-49 = 2000-2049, 50-99 = 1950-1999), or in the form of dd-MON-yyyy (such as 31-Dec-2099), in which case the 4-digit year is used as it is entered (also see Defines Using Select):

set showmode off echo off
set heading off pagesize 0 timing off feedback off termout on
accept fieldval char prompt 'Enter Date (dd-MON-yy or dd-MON-yyyy): '
set termout off verify off
spool fieldina.sql
select 'define fieldval = ' || to_char(decode(sign(9-length('&fieldval')),-1,
     to_date('&fieldval','DD-MON-YYYY'),to_date('&fieldval','DD-MON-RR')),
     'DD-MON-YYYY') from dual;
spool off
@fieldina.sql
rem     your other custom code goes here
rem     !echo &fieldval
set heading on pagesize 24 timing on feedback 6
set termout on verify on echo on showmode both

You can then use the results placed into the fieldval variable in an SQL select statement to compare with dates in the database, such as in:

select * from spriden where spriden_activity_date = to_date('&fieldval','DD-MON-YYYY');

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.