DATE vs CHAR

[Melinda Braddock <braddock@CMCMFG.COM>]

There seems to be alot of discussion of dates lately.  There have been alot
of good solutions given, and I suggest that each of you use the one that
best fits your situation.  But I would like to point out one other thing
about dates that I have not seen in the recent postings.

These observations were made using SQR v4.0 on an Oracle 7.3 database (for
the record).  I don't think DATE datatypes were available with SQR v3.

Remember that a DATE datatype and a CHAR datatype are not the same thing!
My one big gripe with SQR is the way it handles variables -- you don't
actually declare them.  If you spell (and type) as poorly as I do, that can
be a big problem!   Not declaring variables can also lead to mass confusion
over dates.

Consider the following:
BEGIN-SELECT
this_date  &date1
  move &date1 to $new_date1
...
END-SELECT

IF this_date is a DATE field in your database, then SQR knows that your
column variable, &date1, is a DATE.  You can print &date1 in any format you
like using an edit mask.  HOWEVER be aware that $new_date1 is CHARACTER!!!
If you are using an Oracle database like I am with the default mask set to
DD-MON-YY,  then you have lost your time and your century!  You can print
$new_date1 with an edit mask of YYYY and it will stick a '19' onto your year
... even if the year in the database was 2000+.  Not good.  If you try to
compare a character string to a character string, SQR simply compares them
in alphabetical order -- it has no idea that you intended for these to be
dates.  The solution is simple, declare $new_date1 as a DATE.

BEGIN-DECLARE
  DATE $new_date1
END-DECLARE

 As long as you are comparing two variables that are both DATE datatypes,
you should NOT have to change to 'YYYYMMDD'.  You can simply use:  IF
$new_date1 < $new_date2  (or you can use:  IF &date1 < &date2).  All the
stuff about date format ... changing it for your session, changing it for
your database ... that just changes the default way the date is displayed to
you and the default way that you can enter or compare a date. The actual
date itself is not changed.

Also notice the way INPUT handles dates.  Even though you use type=date,
your input variable is still a CHAR!  Read your manual closely on this one.
In order to input a variable as a DATE datatype, you must declare it first!

Melinda