Re: [dba-users] Date Switching Bug in Queries

[Phill Gillespie]

Frank Schönheit - Sun Microsystems, Inc. wrote: c. 1st Dec 2004 in UK format, '01-12-2004' and it suddenly changes to the USA format, rewriting it as #12/01/2004# which it then treats as a UK date (thinking it is 12th January 2004). *sigh*. Cannot reproduce. Could you use the libdbu680li.so from http://dba.openoffice.org/servlets/ProjectDocumentList, and reproduce the bug? You should see some output on the console, which I'd be interested in... (The lib is for a m80 version) Unfortunately I can't get Base up at all if I use this lib instead of the original one :-(   I'm using build m80 made by Pavel Janik.  He's just uploaded m85 if you'd like me to try that. I do have clearer instructions though to reproduce this :-) In the design view (with locale set to UK) create your simple query.  As the criterion enter the following and you should get the corresponding results: 1.   > 01-11-2004   (turns into #01/11/2004# and means 1st Nov 2004) 2.   > 11-01-2004   (turns into #11/01/2004# and means 11th Jan 2004) 3.   > 01-13-2004   (Warning.  The value entered is not a valid date.  Please enter a date in a valid format, for example MM/DD/YYYY) These three examples can be entered again with slashes rather than hyphens (e.g. 01/11/2004) and you should get exactly the results. However, if the date is encapsulated inside single quotes then it starts to misbehave... 4.   > '01-11-2004'   (turns into #11/01/2004# and means 11th Jan 2004) 5.   > '11-01-2004'   (turns into #01/11/2004# and means 1st Nov 2004) It seems adding single quotes makes Base think that the date is in the US format rather than the current locale.  These two steps can be reproduced by using slashes instead of hyphens too, just so long as they are inside single quotes. Putting an unambiguous date in either US or UK format with single quotes accepts it correctly (rather than a warning for US style) 6.   > '13-01-2004'   (turns into #13/01/2004# and means 13th Jan 2004) 7.   > '01-13-2004'   (turns into #13/01/2004# and means 13th Jan 2004) Hope you can see this on your version... P h i l l