Re: Formatting a DATE differently using SQL

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

SELECT
SUBSTR(CHAR(StartDate, USA), 1, 6) CONCAT
SUBSTR(CHAR(StartDate, USA), 9, 2)
, SUBSTR(CHAR(EndDate , USA), 1, 6) CONCAT
SUBSTR(CHAR(EndDate , USA), 9, 2)
FROM
( SELECT
MIN(dayid) as StartDate
, MAX(dayid) AS EndDate
FROM mySchema.ludate
WHERE yearid = '2014'
AND datemn = '01'
) A

...which brings us back to the wordiness <<SNIP>>

If the wordiness is the primary issue, then simply encapsulate the /wordy/ expressions in a VIEW or a UDF. If a UDF is created [or for example the iDate as referred to by someone else is installed and its pre-created functions utilized], then the statement can be much more succinct [and attractive]; e.g. using scalar UDF MMDDYY that takes one argument as DATE data type and outputs a CHAR(8) result which is the character-string representing the DATE value in the form MM/DD/YY:

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