Comparing dates in a query statement

[Wes Peters]

I want to be able to compare a user-supplied date against a smalldatetime
field in a query. It looks like using the # sign is a mdb thing only. Do I
use the Convert function on the table's date field to convert to a
"formatted" smalldatetime and then compare? This is what I've tried with
some success. It works when I use a > or < in the comparison but not =.

What is the best approach to constructing a query string that compares a
user-supplied date against smalldatetime field?

Any thoughts or articles on the subject would be appreciated.

Thanks,
Wes

 

[Sylvain Lafontaine]

On SQL-Server, dates must be enclosed with single quote ' instead of the
pound sign #. With the correct option set, you can also use the double
quote " but it's better to stick with the standard.

You must use the international date format, yyyy/mm/dd or the US format,
mm/dd/yyyy or mm/dd/yy if the default language for the login is english;
otherwise you must use an appropriate format.

The trouble with DateTime and SmallDateTime is that they have both a time
part that may be different from midnight; something that may cause a lot of
problem when used with the equality operator = . The convert function can
be used to strip away this time part; use a second call to the convert
function to convert back to a DateTime or SmallDateTime field if you don't
want some missy resultats with > and < .

Finally, the best way is not to construct the query string but to use a
query with parameters; as this will shield you from potential localization
problems (and security/hacking problems, too).

 

[Wes Peters]

Thanks for the info.

Does the following statement strip the time from a datetime field:

CONVERT (datetime, CONVERT (varchar, tblOrders.DTStamp, 101), 101) AS Placed

When I compare the above field with the same date in an unbound textbox
using single quotes they don't show to be equal and I'm not sure why unless
somehow the time part is still there.

Any thoughts?

 

[Sylvain Lafontaine]

Yes, this should be sufficient to strip the time (or more exactly reset it
to midnight). Personally I use nvarchar(10) instead of varchar but that
shouldn't change anything.

I don't know why this doesn't work with your unbound textbox. You should
print the intermediary result to make sure that everything is OK. Apply the
Convert() function to the value of your textbox to see how it is interpreted
by SQL-Server.

 

[Wes Peters]

Thanks for your help Sylvain.

I finally got it working. Long story short, I was comparing the text box
date against a date field I *thought* I had CONVERTed but hadn't.

Wes