On 12 Apr 2013 06:00, Luis Rodriguez wrote:
<<SNIP>> when you create a DATE from a string in *ISO format, it
does not matter what your DATFMT value is, the date will be created
correctly.
Just like the *ISO date format is always understood by the SQL, so
too are *EUR and *USA [and implicitly *JIS per being identical to *ISO].
That is why the expression I gave in this thread using *USA will work,
just as if I had chosen *ISO.
Converting the string to MMDDYY to *USA is the INSERT of a '/' slash
character after the 'MM', and INSERT of both a '/' slash character and
the result of the numeric expression 19+ODxCEN after the 'DD'; i.e. the
following expression, albeit the DIGITS specification is redundant due
to implicit casting and the numeric result will always be two digits:
date( insert( insert( ODCDAT, 3, 0, '/')
, 6, 0 , '/' concat digits(dec(ODCCEN+19, 2)) ) )
BUT, if you are using, say, DATFMT(*EUR) and your DATE string is
in a non-*EUR format you will have problems. Although in Venezuela
(where I live) the date format is dd/mm/yyyy, I always, always use
*ISO formats in my SQL statements. It avoids a lot of headaches...
That is a mis-characterization. The date format dd/mm/yyyy is
problematic only because of the chosen separator of '/' vs '.'; the
latter is DATFMT(*EUR). Always use properly formed *EUR or *ISO [or
even *USA] and avoid the headaches. FWiW the 'dd/mm/yyyy' is easily
converted to *EUR using the following expression:
replace( 'dd/mm/yyyy', '/', '.' )
While using any other SQL DATFMT() setting, the SQL must always be
able to cast to DATE, each of the first four character expressions on
the select-list of the following example SELECT; that is because each is
a four-digit-year /standards/ format which is perceptively unique and
thus capable of being inferred with an algorithm:
select date('2011-09-30') as jis
, date('2011-09-30') as iso
, date('30.09.2011') as eur
, date('09/30/2011') as usa
, date('11/09/30') as ymd
, date('30/09/11') as dmy
, date('09/30/11') as mdy
, date('11/273 ') as jul
from qsqptabl
The first three of the remaining four expressions may /accidentally/
be interpreted incorrectly as a valid date [per both 09 and 11 are valid
for all of YY, MM, and DD], even if other values outside the 100-year
window may not be able to be /presented/ when any format other than a
4-digit year is used for presentation. But what is merely a
presentation issue, does not affect selection and collation.
midrange.com
