Re: Y2K input

[Steven Calvert <calvert@ULETH.CA>]

Charlie Dornbos wrote:

> How do I get SQR 3.5 using Oracle 7.2 to take YYYY inputs? I can take it as
> a text input but then I have to do all the error checking. If I use the Date
> qualifier SQR wants DD-MON-YY. Can I change this? Currently I am importing
> this as a string (DD-MON-YYYY) and taking it apart a section at a time and
> using a little error checking but I wonder if there is a better way.

This may not be the "best" way, but you can let Oracle check input dates with:

begin-procedure BAD_DATE_INPUT
  let $BAD_DATE_FORMAT = 'Y'
end-procedure ! BAD_DATE_INPUT

begin-procedure MAIN
  :
Invalid_Rep_As_Of:
  input $REP_AS_OF      'Report As Of Date, default = today (DD-MON-YYYY)'
  let $BAD_DATE_FORMAT = 'N'
  begin-select on-error=BAD_DATE_INPUT
to_char(nvl(to_date($REP_AS_OF,'DD-MON-YYYY'),sysdate), 'DD-MON-YYYY')
                                        (  , {1})
  on-break save=$REP_AS_OF print=never
  from system.dual
  end-select
  if $BAD_DATE_FORMAT = 'Y'
    show 'Date format is DD-MON-YYYY.  Please try again.' beep
    show
    show
    goto Invalid_Rep_As_Of
  end-if
! Program gets to here if a valid date (or nothing) is entered ...
  :
! Using the date is a select would then look like:
  begin-select
fmvltyp_ltyp_code                      ( 1, {C1})
  on-break print=always
fmvltyp_title                           ( 1, {C2})
fmvltyp_desc                            ( 2, {C2}) wrap 60 20
  next-listing skiplines=1 need=6
    from fms_dba.fmvltyp
   where fmvltyp_eff_date <= to_date($REP_AS_OF,'DD-MON-YYYY')
     and nvl(fmvltyp_term_date,fmvltyp_nchg_date) >
         to_date($REP_AS_OF,'DD-MON-YYYY')
   order by 1, 2
  end-select
  :
end-procedure ! MAIN

--
Steven Calvert
calvert@uleth.ca
University of Lethbridge
(403)329-2071