On Wed, 09 Nov 2011 23:32:14 +1300, Helen Borrie <helebor@...>
wrote:

> At 11:04 PM 9/11/2011, Mark Rotteveel wrote:
>>On Wed, 09 Nov 2011 09:59:23 -0000, "tomsee7" <tomconlon7@...>
>>wrote:
>>> Also, '30-Jun-2011 08:35:26' is explicit and unambiguous.
>>
>>Is it?
>
> Yes. As is '11/06/2011' - as the OP picked up from my *typo*. That is
> valid for dates in the US (mmddccyy) date format. '11.06.2011' is used

for

> the International English (ddmmccyy)date format.
>
>>Technically it is locale-dependent conversion as well.
>
> Technically it's not. It's not locale-dependent and it's not (in most
> contexts) a conversion but encoding of recognised literal formats. The

fact

> that it uses English abbreviations for calendar months is not due to

locale

> but to a xenophobic implementation.

Maybe technically it isn't for Firebird, but in general terms
'30-Jun-2011' is a locale dependent representation of date (although
probably 10-Feb-2011 or 10-Aug-2011 are better examples).

> Firebird doesn't support "locale conversion" at all. It takes what you
> give it and rejects it if it has a rule that says it's invalid.
>
>>Even better of course would be to simply use a parametrized query which
>>assigns a timestamp value (eg java.sql.Timestamp in JDBC), instead of
>>using
>>string conversions.
>
> Definitely parameters are best practice for date/time literals. Don't
> call the literal encoding "string conversion", though.

It is a string conversion from a date: when I would need to construct a
string like that in my application, I will need to use a locale sensitive
conversion from a date to that specific string format. If I am not careful
(eg not specifying the right locale and depend on the default), I could get
a wrong string.

Eg in Java, the following program:
SimpleDateFormat dfIso = new SimpleDateFormat("yyyy-MM-dd");
SimpleDateFormat dfUS = new SimpleDateFormat("dd-MMM-yyyy",
Locale.US);
SimpleDateFormat dfFR = new SimpleDateFormat("dd-MMM-yyyy",
Locale.FRANCE);

Date date = dfIso.parse("2011-02-10");
String usDateRepr = dfUS.format(date);
System.out.println("US " + usDateRepr);
String frDateRepr = dfFR.format(date);
System.out.println("FR " + frDateRepr);
try {
dfFR.parse(usDateRepr);
} catch (ParseException ex) {
System.out.println("Failed to parse US string with FR
format");
}
try {
dfUS.parse(frDateRepr);
} catch (ParseException ex) {
System.out.println("Failed to parse FR string with US
format");
}

Will output:
US 10-Feb-2011
FR 10-févr.-2011
Failed to parse US string with FR format
Failed to parse FR string with US format

So if I am developing in France, and I am using
SimpleDateFormat("dd-MMM-yyyy") to create date strings for use in my
queries, Firebird will break on the formatting of the French date-string:
that is what I meant with locale dependence. That is why in general it is
preferable to use ISO 8601 (yyyy-MM-dd format) : it removes the locale
dependency.

Mark