Re: Formatting a DATE differently using SQL

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