UBB.Developers Forums Community Development Programming PHP and Server-side Scripting MySQL Query Syntax

I'm missing something obvious here -

Here's my query:


SELECT SUM(Miles) FROM w3t_Miles WHERE User IN (207,215,225) AND Date > 2003-08-01 AND Date < 2004-05-06



OK - this grabs my user numbers 207, 215 and 225. That part works fine. Each record has a date stamp - I want to get ones newer than 8-1-2003 but before 5-6-2004.

Yet it always seems to return no results. If I take the second Date conditional out - it returns results.

Is there a better way in MySQL to grab a range of dates????

Thanks!

SELECT SUM(Miles) FROM w3t_Miles WHERE User IN (207,215,225) AND Date BETWEEN 2003-08-01 and 2004-05-06

Try that and see if it works

Very odd - I must be doing something else wrong - that didn't work either - I even took the user clause out just to simplify:

SELECT SUM( Miles )
FROM w3t_Miles
WHERE Date
BETWEEN 2003-08-01 AND 2004-05-06

Thanks for the suggestion though.

One thing I do when I'm having trouble with a query is I will go and build a mock setup in Access, and play with it in there until I get it tweaked. Then I just use the SQL view to get the syntax that works and port that into my code.

It saves time as opposed to change your code, upload, test. And it doesn't cause any damage to any thing

what format is the actual date stamp Josh is it in UNIX? or GMT as it appears above?

Maybe it is daft but why not try it with enclosing the date in quotes like

....
BETWEEN '2003-08-01' AND '2004-05-06'

your right it should be encased in '

Eureka!

Yup the quotes did the trick. Funny that with only one date clause - it worked without the quote. It's a MySQL Date field so I knew I had to be overlooking the abvious. Thank you!