[AccessD] SQL 2005 - disambiguous DateTime format

Hi Borge

I would put it this way, that DD-MMM-YYYY is a dangerous format to use as you to create the string often use a helper function like - in Access and VBA - Format which localizes the mmm part. Pass that to SQL Server or another engine that expects a US format and it will fail.

As for the ADO Find, look up the thread "ADO Find, serious issue with international dates" in Jan. 2007:

  http://databaseadvisors.com/pipermail/accessd/2007-January/subject.html 

It does not deal with the dd-mmm-yyyy format, but I wouldn't waste any time checking it out. The ISO 8601 format is really the way to go. It is unambiguous in any modern environment, MS prefers it now, MySQL has always had it as the preferred format, Access has never had any trouble with it though - for JET SQL - the US format was the preferred format, and - if the engine allows - you can easily expand it to specify milliseconds too.

/gustav

>>> pcs.accessd at gmail.com 16-08-2008 12:16 >>>
Thanks Gustav, I sort of expected you would be the one chiming in....

Are you saying that DD-MMM-YYYY would get you into trouble using ADO when
filtering on dates?
If so, do you have any documentation for this?

borge

On Sat, Aug 16, 2008 at 6:45 PM, Gustav Brock <Gustav at cactus.dk> wrote:

> Hi Borge
>
> The ISO format, YYYY-MM-DD is the preferred format (just browse the
> documentation) for SQL Server 2008.
> It is certainly safe for 2005 as well and required(!) for ADO when
> filtering on dates.
>
> /gustav
>
> >>> pcs.accessd at gmail.com 16-08-2008 10:05 >>>
> Hi all,
>
> I SQL 2005 I have adopted to routine of always passing a DateTime format for
> whatever purpose in the format of a string:
>
> YYYY-MM-DD
>
> in order to be sure that the date is interpreted correctly irrespective of
> locale setting of the computer
>
>
> Now some colleagues are telling me to use format DD-MMM-YYYY is the -only-
> safe way.
>
> I would think that both methods of passing a date would be -safe-
>
> (To avoid misinterpretation of 4/8/2008 - is it 4 August 2008 or 8 April 2008?)
>
> Any pointers to KB article or other than explains this in more detail?
>
> Regards
> Borge