Why isnt this simple expression working?

[Ngabay]

I would appreciate if you guys could help me with this query.

SELECT *
FROM Activity
WHERE DATE BETWEEN 5 / 1 / 03 AND 5 / 29 / 03

It returns blank rows. What am I doing wrong?


Thanks,
Nir

 

[Swi]

BETWEEN #5/1/03# AND #5/29/03#

Swi

 

[BobRodes]

You need to put the dates in quotes, thus:

WHERE DATE BETWEEN '5 / 1 / 03' AND '5 / 29 / 03'

Assuming that you are using SQL Server and that the type of the date field is datetime, the query processor will translate the string into a date. Your version treats it as 5 divided by 1 divided by 3, truncates the resulting decimal value, leaving 0. It then translates that 0 to the first possible system date (Jan 1, 1753 or close to it) and queries against that date. You can see why you don't get any values.

Bob Rodes

 

[BobRodes]

Actually, you will find that the # operator doesn't work here, since between requires integers to work with and can't translate the date value into integers. However, it can translate a string value, realizing that it has to convert it to an integer date value (some number of days after Jan 1, 1753 or thereabouts, expressed as an integer) on account of the underlying field's being a datetime type.

Bob

 

[Ngabay]

Thanks Guys,

I am using this query to generate a report. Is there a way to build a form that lets the users choose the 2 dates and then the application would automaticly enter the dates in the above query?


Thanks alot,
Nir

 

[BobRodes]

Yes, you can do that in general. However, there are lots of ways to generate a report. Are you using Crystal Reports, or are you using the VB6 Report Designer, for example? As a general rule, you will need to set some sort of property for some sort of report object.

Bob Rodes