Select statement returning no rows

Question

I was asked to write an MYSQL code that returns data from specific dates (2003-2005 December only)from my database. I, however, cannot figure out why I am getting no data returned. The code I am using is below. I tried to use the BETWEEN clause but I still get no data returned. Any help is greatly appreciated.

select 
paymentDate, amount, discounted
From 
payments
where 
paymentDate >= 2003-12-01 and paymentDate <= 2003-12-31
and paymentDate >= 2004-12-01 and paymentDate <= 2004-12-31
and paymentDate >= 2005-12-01 and paymentDate <= 2005-12-31
Order by paymentDate;

I suspect the date field may have a different format. I would suggest selecting a single row with only the `paymentDate` to confirm the format. — mattcan, Feb 01 '20 at 04:31
Additionally to @MattCan 's suggestion. Try to put date in quotes like this paymentDate >= '2003-12-01'. — Svetoslav Dimitrov, Feb 01 '20 at 06:50

mechanical_meat · Answer 1 · 2020-02-01T02:04:51.100

0

Because the paymentDate may not be simultaneously in December of each of those years.
Instead connect each of the December conditional statements with or.
Also I included ANSI syntax for the dates, i.e. date '2003-12-01'

where 
(paymentDate >= date '2003-12-01' and paymentDate <= date '2003-12-31'
 or paymentDate >= date '2004-12-01' and paymentDate <= date '2004-12-31'
 or paymentDate >= date '2005-12-01' and paymentDate <= date '2005-12-31')

edited Feb 01 '20 at 02:04

answered Feb 01 '20 at 01:55

mechanical_meat

163,903
24
228
223

Thanks for the help Vorsprung durch Technik but it is still returning no data. I am also certain that the table does contain data for December in all of those years. – testmysqllife Feb 01 '20 at 02:03
You're welcome. I made an edit to my answer. Would you try that out? – mechanical_meat Feb 01 '20 at 02:05
Yes, success! That was the issue. Funny thing is I tried that but I think I had the DATE in the wrong place. Thank you for your help. – testmysqllife Feb 01 '20 at 02:13

Select statement returning no rows

1 Answers1