On 11 Apr 2013 14:50, Stone, Joel wrote:
I thought I was complete in my request for help, but I will try to
be more complete.
Complete enough :-) Note that this message thread was described as
being explicit on the record format being used and naming the column of
interest, specifically *in contrast* to what it might have been; i.e.
talking-up what was given. I think this issue was much more complete
than many other SQL related inquires.
The commentary about descriptions being incomplete was more general,
but did specifically address a _speculation_ as to one _possibility_
presented by Rob, that there was something more; i.e. something that may
have been left out in this message thread, and which I supposed might be
inferred from a separate recent thread.
The great response you provided as follows:
If using *MDY for the DatFmt, and ignoring the ODCCEN is
acceptable, then the following expression is fairly succinct
[all dates must be in the 1940-2039 100-year window]:
date(insert(insert(ODCDAT, 5, 0, '/'), 3, 0, '/'))
OK... so _that_ was the chosen expression. No mention however, if
that was to be used for presentation only, or if also for ordering.
I was not sure to which of the query or the two expressions that I
had offered, that the comments about " _This_ works great ... possible
to ensure _this_ functions properly even if a user does a CHGJOB
DATFMT(*YMD) and then uses the SQL suggested below" had actually
applied; i.e. of the three, I could not know to what "this" referred.
My immediate thought was that both the query and the expressions will
all work, and the data for the field ODCDAT remains *MDY, irrespective
of the job date format. That is why I responded saying only that
effectively, the ODCDAT is in the form MMDDYY irrespective of the job
date format; that only the ODDDAT varied according to job date format.
This SQL stmt works GREAT if the CHGJOB DATFMT(*MDY) is in force.
Unfortunately this SQL stmt fails if someone did
CHGJOB DATFMT(*YMD) which often occurs.
Ah-hah! I understand the concern now. The /job date format/ is not
germane; except when *JOB is the chosen DATFMT for the SQL environment.
While consistency in naming of OS and related features' terminology is
nice, that can also be an issue for clarity.
My reference to the "DatFmt" was about the *SQL Date Format* rather
than about the Job Date Format; i.e. in reference to the SET OPTION
DATFMT=*MDY [or an equivalent]. I made the mistake of /assuming/ that
since I was talking about the SQL, that my DATFMT reference would be
understood to be the SQL Date Format, instead of having explicitly
stating that.
My question is: would it be possible to ensure that the above SQL
stmt is successful regardless of the current job's DATFMT setting.
So, per above... What job date format in effect, is of no
consequence. If the SQL date format is set to *MDY when using that
expression as the choice for establishing a DATE data type from a valid
representation of a CHAR(6) with the MMMDDYY format, the expression
should always work. Encapsulating that expression in a routine or
embedded SQL that can have its SQL DATFMT established by SET OPTION,
ensures that the DATFMT for the job is always immaterial.
In a SELECT [perhaps in a VIEW] however, the DATE casting function
will not produce desired results when someone has not established the
SQL DATFMT(*MDY). :-( But...
That can be resolved in presentation [not collation] by *not casting*
the value to a DATE; i.e. leaving the data appear as the character
string MM/DD/YY. Just as I had noted humans are adaptable enough to
interpret correctly, generally, the values of MMDDYY, so too should they
be able, and even more likely to do so correctly, interpret the meaning
of the presentation of values as MM/DD/YY; i.e. use the following
expression instead:
insert(insert(ODCDAT, 5, 0, '/'), 3, 0, '/') /* no DATE() cast */
If the value to cast from character date representation uses a
standards-recognized four-digit year format, then the expression will be
able to cast to a date value irrespective of the SQL Date Format. Plus
ensuring all values are in the 100-year window is also no longer an
issue, for evaluating the expression or ordering, but any value outside
the 100-year window would cause an issue for anyone running with a SQL
DatFmt that supports only a 2-digit year. Thus the other expression
that I gave can be used in both ordering and presentation [for all date
values in the 100-year window], because it utilizes the *USA standard
date format:
Avoiding both the 6-digit year format and the ignoring of the
ODCCEN, to ensure the date is processed to the correct century,
the following expression formulating a *USA date format is a bit
longer:
date( insert( insert( ODCDAT, 3, 0, '/')
, 6, 0 , '/' concat digits(dec(ODCCEN+19, 2)) ) )
As Rob alluded in a prior reply, there is probably little reason to
actually cast to date in the given scenario... unless the data is going
to be stored somewhere. But I must warn if one were to cast to date, be
aware that the object dates have an anamoly, albeit unlikely to be
experienced in any /Latin/ language based system, whereby the date can
be an invalid 29-Feb in a year of the Gregorian calendar which is not a
leap year; the /leap adjustment/ is not stored with the date value, so
they are not un-adjusted when the date values are retrieved [e.g. from a
creation date].
I personally would stick with the query I offered whereby the ODCDAT
is left in its unedited MMDDYY form, and sort accordingly. If the
scenario describes something that will be developed into tooling that
will be used generally, and the DATFMT of the job should be honored, I
would probably create and use a UDF to generate the 4-digit year
character form, cast it to date, and maybe even adjust any bad leap
dates or maybe return a NULL or hi\lo date value. I believe I have
published such a UDF here before, including the leap adjust.
Regards, Chuck
CRPence on Thursday, April 11, 2013 3:51 PM wrote:
On 11 Apr 2013 13:23, rob@xxxxxxxxx wrote:
Yes, you're right. The format in that file stays consistent.
Perhaps he was concerned about what he's comparing it with.
Well like most posts asking for help, they are woefully incomplete
for what are the actual requirements; all too many assumptions must
be made by the reader. At least this was not another intentional
obfuscation for which perhaps there would have been no mention that
the format for QADSPOBJ was being used; instead just a mention of
some MMDDYY character field, possibly not even clarified char vs
numeric or even possibly no type or length having been noted, then
from a new message twenty posts later or even in a new message
thread, mostly just bad guesses and\or questions trying to pry
relevant details, to learn that there was a /century/ indicator
column in the database file all along!
So anyhow... I did not recall any reference to a value to which
there would need to be a comparison *in this message thread* so I
figured there was no such need :-) Taking into account a prior
message thread, perhaps with the subject "Query/400 list of objects
by date", one might infer that the eventual goal might include
*selection* from that "list" of *QRYDFN objects in the DSPOBJD output
file rather than just *ordering* the list, whereby there is a
particular object with a date of "March 31" give-or-take several
days. But that was a different thread, oh so long ago ;-)
midrange.com
