RE: Error on update in SQL

["Goodbar, Loyd (AFS-Water Valley)" <LGoodbar@xxxxxxxxxxxxxx>]

According to the SQL reference, when the char() function transforms an
integer, only significant digits are returned. In the case of
char(month(date('2002-08-02'))), month() will be 08, and the char() of that
is "8".

The date() transformation, and the SQL parser, believes that '2002-08-02'
and '2002-8-2' are dates because that are formatted as an ISO date. Leading
zeroes are not required.

To duplicate your issues, I tried
  char(year(curdate()))||'-'||
  char(month(curdate()))||'-'||
  char(day(curdate()))
Which returned "2002       -8          -2          ". Not good.

Then I tried
  strip(char(year(curdate())))||'-'||
  strip(char(month(curdate())))||'-'||
  strip(char(day(curdate())))
Which returned "2002-8-2" This should be a "valid" ISO date.

Taking the above output "2002-8-2" I want to see if it creates a date from
it.
date( strip(char(year(curdate())))||'-'||
  strip(char(month(curdate())))||'-'||
  strip(char(day(curdate()))) )
Which returns "08/02/02" (date in *USA format)

In your specific case of populating a YYYYMMDD char from a date, the
following will return "20020802":
  substr(char(curdate(),ISO),1,4)||
  substr(char(curdate(),ISO),6,2)||
  substr(char(curdate(),ISO),9,2)

The char() function returns "2002-08-02", from which the component parts,
with leading zeroes that you expect, are extracted.
The char() function also allows an optional variable to specify the format
of the converted date field, much like factor 1 of the MOVE operation for
dates.

HTH,
Loyd

-----Original Message-----
From: McCallion, Martin [mailto:martin.mccallion@misys.com]
Sent: Friday, August 02, 2002 5:29 AM
To: 'midrange-l@midrange.com'
Subject: RE: Error on update in SQL


Loyd Goodbar wrote:

> This works with a date datatype
>
> update spendmsa set
> asofdate = '2000-'||strip(char(month(asofdate)))||'-'||
> strip(char(day(asofdate)))

I'm interested in this, because I had to do some complicated hacking on an
SQL date field just yesterday.  I had to initialise an eight-byte character
field with a date in the form YYYYMMDD, using the curdate() function to get
the current date.  I was trying to use char(year(curdate())),
char(month(curdate())) and char(day(curdate())) concatenated together, but
the result was too long.  According to the InfoCenter, year() etc return
long integers.

So I tried using strip() (or trim()) to remove the leading zeros, which I
assume is what you are doing above; but you don't seem to specify '0' as the
character to strip.  And of course, for months before October and days
before the tenth, the results are not what you want.

I had to use a series of substring operations, but a solution like the above
would have been neater; so what am I not getting?

TIA

Cheers,

Martin.