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

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 ;-)

This thread ...


Return to Archive home page | Return to MIDRANGE.COM home page