|
|
Cooperative Extension Service |
|
 |
||
|
|
||
|
Agricultural
Experiment Station |
||
 |
|
|
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');
This Page was Last Updated on 06/04/07
You Are Visitor Number |
|
|
© 2006 |
|
|
University of Arkansas • Division of Agriculture |
Mission
•
Disclaimer
•
EEO
•
|
