CODEWITHSUNDEEP
phpmysqlsql
Mrinal G.
Mrinal G.

Reputation: 11

SQL IN operator not working when use other condition

SELECT * FROM schedule_backup WHERE shift_id IN (2, 7, 9) AND date BETWEEN '2013-05-22' AND '2013-05-22'

the above query not working, it show empty row selected

but the below is working

SELECT * FROM schedule_backup WHERE shift_id IN (2, 7, 9)

Upvotes: 1

Views: 86

Answers (3)

Stickman
Stickman

Reputation: 136

Personally, I like to use the datediff function:

SELECT * FROM schedule_backup WHERE shift_id IN (2, 7, 9) AND
DATEDIFF(date,'1/1/2013') = 0

It's much more apperent as to what you're doing and you won't accidentally pull in another day or exclude a record at the very end of the day as the between operator will do.

Another alternative is to simply cast to date:

CAST(dateField AS date)

EDIT: Modified to MySQL syntax.

Upvotes: 1

Nagarjun
Nagarjun

Reputation: 2476

BETWEEN '2013-05-22' AND '2013-05-22' - this is something like get numbers between 0 and 0. Change it to two different dates or two different times if it is the same day.

Upvotes: 0

Kermit
Kermit

Reputation: 34063

BETWEEN '2013-05-22' AND '2013-05-22'

If you have a datetime column, this translates to

BETWEEN '2013-05-22 00:00:00' AND '2013-05-22 00:00:00'

I think you want

BETWEEN '2013-05-22' AND '2013-05-23'

...assuming that you actually have records with a date of 2013-05-22 and just want a single day.

Upvotes: 3

Related Questions