Using # in SQL Queries 3

[RichardF]

Hi,

This one has me stuck. Heres the test data in the table :

Name StartDate
------- ----------
Richard 03/05/2003
John 04/05/2003
Jo 06/05/2003
Steve 07/05/2003
Harry 05/06/2003
Sammy 05/06/2003
Ted 09/06/2003

One of my developers asked me why does Statement A return different rows from Statement B ? (date format is dd/mm/yyyy)

Statement A:
SELECT * FROM Table1
WHERE startdate >= #03/05/2003# and startdate <= #05/06/2003#

Results:
Name StartDate
Richard 03/05/2003
John 04/05/2003
Jo 06/05/2003

(wheres sammy, steve and harry ? )

Statement B:
SELECT * FROM Table1
WHERE startdate >= datevalue('03/05/2003') and startdate <= datevalue('05/06/2003')

Results:
Name StartDate
Richard 03/05/2003
John 04/05/2003
Sammy 05/06/2003
Jo 06/05/2003
Steve 07/05/2003
Harry 05/06/2003


(using msaccess 2k, win2k)


thanks in advance.

 

[swampBoogie]

SELECT * FROM Table1
WHERE startdate >= #03/05/2003# 
and startdate <= #05/06/2003 23:59:59#

 

[RichardF]

That yields the same results as Statement A.

And so does:
SELECT * FROM Table1
WHERE startdate >= #03/05/2003 00:00:00#
and startdate <= #05/06/2003 23:59:59#

 

[MichaelRed]

So, deriving from [swampBoogie's and RichardF's examples, you did not present the data in the table completly or accuratly. From a brief look, both statements rturn the same results - which match NEITHER of the ones you posted.

The (more or less obvious) conclusion for me is that you have international settings which are NON-U.S. and this is creating some cofusion (on your part) re the proper formulation of the queries.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GHolden]

I think that you'll find that the reason is that using the # symbol either side of a date implies US format.

Take the following example...

'Locale date setting is UK dd/mm/yy
Dim dteDate As Date

dteDate = #1/12/2001#
MsgBox Format(dteDate, &quot;dd/mmm/yyyy&quot

Returns 12/Jan/2001 (US format)

dteDate = &quot;1/12/2001&quot;
MsgBox Format(dteDate, &quot;dd/mmm/yyyy&quot

Returns 01/Dec/2001 (Local format)



There are two ways to write error-free programs; only the third one works.

 

[RichardF]

MichaelRed:
>> you did not present the data in the table completly or accuratly
I dont want to patronise you, but im not stupid. I know i copied both the contents of the table and each exact query with respective results straight from access.

I also know that both statements should yield the same result. Well almost.

GHolden:
Thanks for pointing out to me that # implies US date format. Whereas DateValue() uses the current date format.

Thanks,
Rich.

 

[r937]

# does not imply US format, US format is only assumed if there is ambiguity

#21/5/2003# is not ambiguous

#2003-05-21# works fine always (it is assumed to be ISO standard format)

rudy

 

[GHolden]

r937 your are right, I should have clarified. However you need to aviod using the # if you want UK dates, otherwise some dates will be treated as US formt.

Further clarification below (From MS)

When you specify the criteria argument, date literals must be in U.S. format, even if you're not using the U.S. version of the Microsoft Jet database engine. For example, May 10, 1996, is written 10/5/96 in the United Kingdom and 5/10/96 in the United States. Be sure to enclose your date literals with the number sign (#) as shown in the following examples.

To find records dated May 10, 1996 in a United Kingdom database, you must use the following SQL statement:

SELECT *
FROM Orders
WHERE ShippedDate = #5/10/96#;

You can also use the DateValue function which is aware of the international settings established by Microsoft Windows. For example, use this code for the United States:

SELECT *
FROM Orders
WHERE ShippedDate = DateValue('5/10/96');

And use this code for the United Kingdom:

SELECT *
FROM Orders
WHERE ShippedDate = DateValue('10/5/96');



There are two ways to write error-free programs; only the third one works.

 

[RichardF]

GHolden that is a little gem of information.

Here, have a star

-

&quot;Programmers are tools for converting caffeine into code - BSRF&quot;

 

[GHolden]

Cheers Richard, believe me I've battled with this one for hours in the past...

Probably my own fault for not living in the US

There are two ways to write error-free programs; only the third one works.

 

[Quehay]

GHolden thank you for that tip! Another one of those &quot;Why didn't you tell me?&quot; &quot;Well, you didn't ask...&quot; items.

 

[apkohn]

Alternatively for those of us that use correct date format (dd/mm/yyyy) you can try

&quot;SELECT * FROM

WHERE [Date] < #&quot; & format([TheDate],&quot;mm/dd/yyyy&quot & &quot;#&quot;

Its a pain, but reliable.

 

[RichardF]

I 've got related question on this on actually :

When im using DateValue how do i query on date and time.

e.g.
SELECT * from addresses where startdate > #05/03/2003 12.00.00#

Thanks in Advance.
Rich


&quot;Programmers are tools for converting caffeine into code - BSRF&quot;