Date Range

  • Thread starter Thread starter Vayse
  • Start date Start date
V

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
 
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)
 
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 ).
 
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?
 
Yes, it can make a difference in many (but not all) cases if this time
element is different from 0 or midnight.
 
Back
Top