Re: Dates in "Where" Clause

[Nathan Treadway <nathant@ONTKO.COM>]

> Select Column_1 &Column_1
> Column_2  &Column_2
> From PS_TABLE where Date = $Var_Date
>
>                 ! right now $Var_Date is manipulated into this format:  30-JUN-97
>
> Although the above statement works with the stated format I believe that I
> will have a problem after 01/31/1999 ?!?
>
> When attempting to use other formats the SQR halts and states that I have an
> improper month.
>
> I would like to use a format such as this:  '1997-06-30'   but again I get
> an error message that I have an improper month.  So what I do is take the
> above format ('1997-06-30' ) and string manipulate it into '30-JUN-97'
> format.

The short answer is that you can use the oracle function TO_CHAR to convert
the string '1997-06-30' to a date.  This would look like

from PS_TABLE where Date = to_char($var_date,'yyyy-mm-dd')

Note that this, technically, does not "string manipulate it into
'30-JUN-97'", but rather converts the input string of the specified format
directly  into oracle's internal date representation.

To make life easier for yourself, you should define the date format mask in
a #define variable and should use explicit to_date and to_char functions any
time you are converting between date and string. (An example of the reasons
this is a good idea: it is possible to configure Oracle's "native" date
format to something other than 'dd-MON-yy'; if you don't have explicit date
conversion functions in your program, it will fail if you try to run against
another database with a different configuration -- and such changed
configurations are becoming more common as more places deal with the year
2000 problem.)

So, you'd have a

   #define DATEMASK 'yyyy-mm-dd'

at the top of your program, and then

   begin-select
   to_char(date, {DATEMASK})    &date
   from XXXXX
   where Date = to_char($var_date, {DATEMASK})



Two final notes: 1) SQR v4 does date processing internally, so programs written
for v4 can also use SQR functions instead of the database server
functions.  2) if you are using PeopleSoft, they provide extensive
infrastructure for date handling, and you should use it instead of the
recommendations above.

                                                Nathan

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