SQL Query not getting executing - returns empty set

Question

I have a database table with date field whose data type is also date and i want to fetch those recods which lie betwnn two dates.

My query is :

SELECT * FROM wp_races_entry  WHERE date_in >=2012-02-08 && date_in<=2012-02-27

i also tried

SELECT * FROM wp_races_entry WHERE date_in  BETWEEN 2012-02-08 AND 2012-02-27

i have records in table with date 2012-02-14 but still it return empty value.

Please help me guiding what i am missing exactly.

Any chance you could show us your table's field list and data types? And a few of the rows, some in the date range and some not. Also, if you just do SELECT * FROM wp_races_entry do you get all rows back as expected? (Should SQL dates not be formatted as #yyyy mm dd#?) — JTeagle
– JTeagle, Commented Mar 15, 2012 at 12:36
@JTeagle - Different flavours of SQL have different ways of representing date data-types. One thing that is reasonably universal is to use a string data-type and allow it to be implicitly converted to a date data-type. That has it's own pitfalls ('01-Jan-2012' won't work in France, etc), but '20120101' is pretty common practice — MatBailie
– MatBailie, Commented Mar 15, 2012 at 12:44

Mark Byers · Accepted Answer · 2012-03-15 12:34:43Z

5

You need quotes round your dates:

SELECT * FROM wp_races_entry WHERE date_in BETWEEN '2012-02-08' AND '2012-02-27'

Without the quotes your dates are treated as arithmetic expressions: 2012-02-08 = 2002.

The query you posted is equivalent to this:

SELECT * FROM wp_races_entry WHERE date_in BETWEEN 2002 AND 1983

answered Mar 15, 2012 at 12:34

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MatBailie · Accepted Answer · 2012-03-15 12:35:31Z

5

2012-02-08 isn't a date, it's an integer calulation that yield the result 2002. This is then implictly cast into a date, with 2002 meaning 2002 days from the base date

Instead, use '2012-02-08' which is a string, which is also implicitly cast into a date, but the one you want.

SELECT * FROM wp_races_entry WHERE date_in BETWEEN '2012-02-08' AND '2012-02-27'

answered Mar 15, 2012 at 12:35

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Comments

On the run · Accepted Answer · 2012-03-15 12:55:22Z

1

Try:

SELECT * FROM wp_races_entry WHERE date_in>= '2012-02-08 00:00:00' and date_in<= '2012-02-27 00:00:00'

edited Mar 15, 2012 at 12:55

answered Mar 15, 2012 at 12:37

On the run

395 bronze badges

2 Comments

Rene Pot Over a year ago

it's a DATE field, not a DATETIME field. Therefore false.

MatBailie Over a year ago

And the OPs >= and <= have spontaneously changed to > and < for no apparent reason.

Collectives™ on Stack Overflow

SQL Query not getting executing - returns empty set

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related