RE: SQL and Date Data Types

Jay of US "eh",

Sigh... judging from your sarcasm, I'd say that I've offended you in some
way. Sorry about that. I didn't mean to ruffle any feathers.

Back to the topic...

I'm not sure where your disagreement lies. As you've noted, *ISO is
"ccyy-mm-dd". That is the only format that can be called *ISO, and that is
the one that I mean't. Similarily, when I referred to the others, I assumed
that people would recognize those to be:

*USA - 'mm/dd/ccyy'
*EUR - 'dd.mm.ccyy'
*JIS - 'ccyy-mm-dd'

However, I do note some confusion in your note about why your '10/26/2000'
worked in your SQL. It's not because your job was defined as *MDY, it's
because SQL recognizes your example as *USA. It will know this regardless of
what DATFMT/SEP is specified in your job description. You could leave your
date format to *MDY, specify your literal as '26.10.2000', and it will still
work. It's when you're specifying a format other than the four I mentioned,
that your literal must match (exactly) the job (or program) format. In other
words, '10.26.00' would not work, nor would '26/10/00', etc.

By the way, the "Canada" is added to my sig because there was/is another
fellow named John Taylor posting on this list. If I recall correctly, he was
from somewhere in the U.K. We needed a way to distinguish who was posting
what, and I'd rather not put my employer's name in my sig due to potential
liability issues - hence the country of origin.

Besides, I AM A CANADIAN, and proud of it - so back off hoser! ;-))


Regards,

John Taylor
THE CANADIAN.. eh...


> -----Original Message-----
> From: owner-midrange-l@midrange.com
> [mailto:owner-midrange-l@midrange.com]On Behalf Of Jay Peasley
> Sent: Thursday, August 17, 2000 8:09 PM
> To: MIDRANGE-L@midrange.com
> Subject: Re: SQL and Date Data Types
>
>
> John of Canada,
>
> Isn't the *ISO format 'ccyy-mm-dd'? The files I query all the
> time have the
> date format of *ISO. If I use '20001026' or '2000/10/26' the
> query fails. It
> works fine if I define it the literal as '2000-10-26'. Yes, my job had a
> format of MDY and a seperator of '/', so a literal qualified as
> '10/26/2000'
> will work, but I do feel the correct seperator is needed.
>
> Jay
> US eh
>
> ----- Original Message -----
> From: "John Taylor" <john.taylor@telusplanet.net>
> To: <MIDRANGE-L@midrange.com>
> Sent: Thursday, August 17, 2000 5:42 PM
> Subject: Re: SQL and Date Data Types
>
>
> > Jay,
> >
> > It's not necessary to match the character literal to the format of the
> date
> > field. It's only necessary to use one of the formats that SQL can easily
> > identify (*USA,*ISO,*EUR,*JIS), or to format it according to whatever
> > format/separator is in effect for the job (or program if it's embedded
> SQL).
> >
> > Regards,
> >
> > John Taylor
> > Canada
> >
>

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---