Re: Formatting a DATE differently using SQL

[Robert Clay <zreclay@xxxxxxxxx>]

Rob, I'm sure you're trying to make a point here with the ERP package 
analogy but it eludes me.  Testing would prove that the results with 
LOCAL wouldn't be desirable and therefore, one would fall back to 
substringing and concatenation.

However, I DO see your point with using LOCAL, though. It's also good to 
have the USA substring + concatenation method available for the archives.

That being said, my sample:

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

...becomes:

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 despite the dependability.

I'm really learning a lot today and I appreciate everyone's input.

Robert

"Contrariwise, if it was so, it might be; and if it were so, it would 
be; but as it isn't, it ain't. That's logic."--Tweedledee
On 09/05/2013 2:12 PM, rob@xxxxxxxxx wrote:
> Do not use LOCAL.  Your subject to the vagaries of the machine.  Let's say
> you load erp package BEHEMOTH on.  And in the fine print install
> instructions you see "thou shalt use *ISO everywhere on your job
> descriptions, etc".  Sure, it may vex you.  But are you really going to go
> to your bosses, bosses, bosses, boss with that concern?  Don't you think
> you'll look like a child?
>
> Instead, be specific.  Use my example with USA.
>
>
> Rob Berendt