Re: to_date

[Nathan Treadway <nathant@ONTKO.COM>]

> > I'm having a problem using the oracle to_date function in SQR V3.0.12.1
>
> I get the error message :
>  (SQR 4008) Unknown function or variable in expression: to_date
>          to_date($end_date,'yymmdd') < to_date($begin_date,'yymmdd')
[...]
>    show (8,20) 'Enter Ending    EDI Date [DD-MON-YY] '
>     input $end_date type=date status=#end_date_status noprompt
>       if #end_date_status <> 0 or
>           isnull ($end_date)   or
>             to_date($end_date,'yymmdd') < to_date($begin_date,'yymmdd')
>             show (8,20) clear-line
>             goto END_DATE
>       end-if

The problem here is that you are trying to use a database server function
as an SQR function.  (Notice that to_date isn't a function listed on page
229 of the SQR v3 manual.)

You can do something like this:
-------------------------------------------------------------------------
     input $end_date type=date status=#end_date_status noprompt
       do reformat_dates
       if #end_date_status <> 0 or
           isnull ($end_date)   or
             &end_date_compare < &begin_date_compare
             show (8,20) clear-line
             goto END_DATE
       end-if
       ....

begin-procedure reformat_dates
begin-select
to_char(to_date($begin_date,'dd-mon-yy'),'yyyymmdd') &begin_date_compare
to_char(to_date($end_date,'dd-mon-yy'),'yyyymmdd')   &end_date_compare
from dual
end-select
end-procedure
-------------------------------------------------------------------------
Note that you may want skip SQR's date format checking on the input command
and replace it with your own so that you can more flexibly handle years
in the next century.  For example, you can put an on-error handler on
a select similar to the one above in order to trap user input that does not
match the format mask you give.

                                                        Nathan

----------------------------------------------------------------------------
Nathan Treadway    | Ray Ontko & Co. | info@ontko.com (auto-reply server)
nathant@ontko.com  | Richmond, IN    | ftp.ontko.com, http://www.ontko.com/