SQL Problem

JohnnyBravo

SELECT DISTINCT Count(Accepts.Queue)
FROM Accepts
WHERE Accepts.Queue='31000 - France' And (Accepts.TimeOfAccept Between 5/4/2002 And 7/5/2002);


Anyone can tell me where this is wrong

It has a datatype mismatch

seamus

Originally posted by JohnnyBravo
SELECT DISTINCT Count(Accepts.Queue)
FROM Accepts
WHERE Accepts.Queue='31000 - France' And (Accepts.TimeOfAccept Between 5/4/2002 And 7/5/2002);


Anyone can tell me where this is wrong

It has a datatype mismatch

Edit: Actually, rethunk what I just said....

Perhaps the part '31000 - France' is being mistaken as a mathematical function, but I doubt it.

Maybe the dates need to be enclosed in single quotes?

asmith

Don't know what database you're using but if it's Oracle here's how I'd write it.

SELECT Count(*)
FROM Accepts
WHERE Accepts.Queue='31000 - France' And (Accepts.TimeOfAccept > to_char('05/04/2002', 'DD/MM/YYYY') and Accepts.TimeOfAccept < to_char('07/05/2002', 'DD/MM/YYYY'));

Since count() is a group function, having 'distinct' in front of it has no effect; there would only ever be one row returned.

amen

if you are using MS SQL
you could just do
SELECT Count(*)
FROM Accepts
WHERE Accepts.Queue='31000 - France'
AND
Accepts.TimeOfAccept Between '5/4/2002' And '7/5/2002'
If it is MS SQL then if no time is specifed sql assumes that 07/05/2002 is really
07/05/2002 12:00am
so your between clause would only return date between
05/02/2002 12:00am and 06/05/2002 11:59:59PM
and you would be missing data for 07/05/2002

Morpheus

It could be that Your dates need to be enclosed in quotes, depending on whether they are character strings or coming directly from a datetime field in a table

SELECT DISTINCT Count(Accepts.Queue)
FROM Accepts
WHERE Accepts.Queue='31000 - France'
And (Accepts.TimeOfAccept Between '5/4/2002' And '7/5/2002');

just double check all the datatypes of the fieldnames your using?

HTH