Date Range

[Vayse]

Hi
In an mdb, if I wanted to report by dates, I would do the following
stCriteria = "BETWEEN #" & Amer_Date(me.txtStartDate) & "# AND #" &
Amer_Date(me.txtFinishDate) & "#"

Amer_Date is just a function that changes the date to an American format.
This doesn't work in an ADP. I've tried using ' instead of #, but the date
range seems incorrect. The lower date works fine, but items on the
FinishDate won't appear.
For example, if I do a November test BETWEEN 10/11/06 and 13/11/06, the
13/11/06 stuff won't appear. If I set the upper date to the 14th, the 13th
data appears.
Does BETWEEN work different in SQL? Or is there something else I'm doing
wrong?
Thanks
Vayse

 

[Sylvain Lafontaine]

First, you are correct: you must replace the # with single quote on
SQL-Server. However, take care with this american format as SQL-Server
won't always interpret it in the american format: the language of the Login
might change the format used by default.

On SQL-Server, dates are associated with a time and if the time is not set
to 0 (or Midnight), the upper range will have the problem that as you have
described. You must either make sure that the date field doesn't have a
time component (ie. is set to midnight) or truncate it in the select
statement or replace the Between with >= and < . If I remember correctly,
here a some exemples (not tested):

Update MyTable
set MyDateField = Convert (datetime, Convert (char (10), MyDateField, 101))

or:
.... Where MyDate >= Amer_Date(me.txtStartDate) and MyDate <
Amer_Date(me.txtFinishDate)

 

[Douglas J. Steele]

Won't yyyy-mm-dd always be interpretted correctly, Sylvain?

 

[Sylvain Lafontaine]

No, yyyy-mm-dd will be interpreted as yyyy-dd-mm on any system with dmy as
the default format:

set dateformat dmy
select Month ('2006-01-02')

The only true independant format on SQL-Server is the old ODBC canonic form:
{ d'yyyy-mm-dd' }
(and the other one with the time added: ts ).

 

[Vayse]

Thanks. While the date values being entered have no time element, the field
I am checking against (CustomerLastEdited) does have a time element. Does
this make a difference?

 

[Sylvain Lafontaine]

Yes, it can make a difference in many (but not all) cases if this time
element is different from 0 or midnight.