Re: Formatting a DATE differently using SQL

On 05 Sep 2013 11:07, Robert Clay wrote:

I tried making the changes *YMD and *PERIOD (just to test) in the
JDBC settings in Run SQL Scripts... and the session re-connected
successfully. But, when I ran the statement, I still got MM/DD/YY
format with LOCAL:

SELECT CHAR(StartDate, LOCAL), CHAR(EndDate, LOCAL)
FROM (SELECT MIN(dayid) as StartDate, MAX(dayid) AS EndDate
FROM mySchema.ludate
WHERE yearid = '2014' AND datemn = '01'
) A

The connection setting to effect *YMD is the effective equivalent to the SET OPTION DATFMT. In that case, to use the option that was established for the SQL environment\session, omit the "LOCAL".

The LOCAL setting is a redirection to the DATFMT() of the /job/ in which the SQL runs; i.e. the DATFMT() comes either from the date format resolved at job initialization, or from the date format established via CHGJOB DATFMT(as_desired).

To get the *YMD in the given scenario, the first line of the query would be modified to the following, whereby neither invocation of the CHAR cast function would specify a second argument:

SELECT CHAR(StartDate), CHAR(EndDate)