What's wrong with this query???

Databases
1

Using MySql

This query is returning items outside the date range specified. I can’t figure out why?


SELECT
	tblOrderLines.ProductID,
	tblOrderLines.PersonID,
	tblOrderLines.OrderID,
	tblOrders.OrderDate,
	tblOrderLines.ProductDesc,
	tblOrderLines.Qty,
	tblOrderLines.ItemTotal
FROM
	tblOrders
INNER JOIN tblOrderLines ON tblOrders.OrderID = tblOrderLines.OrderID
INNER JOIN tblProducts ON tblOrderLines.ProductID = tblProducts.ProductID
WHERE
	((
		tblOrders.OrderDate BETWEEN '20103-01-01'
		AND '2014-12-31'
	)
AND 
	(CategoryDescription = 'Books'
	or CategoryDescription = 'Other'
	or CategoryDescription = 'Reports'
	or CategoryDescription = 'Actionware'
	or CategoryDescription = 'Items'
	or CategoryDescription = 'Giftware'
	or CategoryDescription = 'Webinars'
	or CategoryDescription = 'Education'
	or CategoryDescription = 'On Line Course'
)
)
ORDER BY
	tblOrders.OrderDate, tblOrderLines.ProductDesc ASC

It’s returning records from 1997, the beginning of the database.

Anybody have a clue?

Thanks

Rich

2

One obvious problem is

‘20103-01-01’

thats not a valid year

3

Another thing to consider is ‘how is the date stored in the database’?
I seem to recall that in MySQL its not a date field per se, but a string

If your stored dates are held as ‘25 April 1997’ instead of ‘1997-04-25’ then a string compare will put 25 after 2010 and return an unexpected row

4

Not directly related to your question… but you might use this syntax for readability and a single function

CategoryDescription IN(‘Books’, ‘Other’,‘Reports’,‘Actionware’,‘Items’,‘Giftware’,‘Webinars’, ‘Education’,‘On Line Course’)

5

… From tblOrders, tblOrderlines …

6

… And why do you inner join on tblProducts?

7

Jeff,

You get the prize!!!

Amazing how you can look at something for so long and not se the obvious!

I am embarrassed to say the least!

Thanks