CODEWITHSUNDEEP
sql
Kehinde Ogundeyi
Kehinde Ogundeyi

Reputation: 77

Sql query did not return all values when using between clause

I have a query :

select *
from indicatordetails
where indicator.month between 'January'and 'April'

When I run it it returns no result, but when I use:

select *
from indicatordetails
where indicator.month between 'January'and 'march'

It returns result.

Note: my table have results starting from January to April, I will appreciate any help, thanks

Upvotes: 1

Views: 67

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

What you have as a "month" is a string. The operands for between are ordered. And as strings, 'January' is after 'April'.

This is more easily seen with numbers. This construct:

where x between 9 and 1

will never return any results, because 9 > 1. This might return results if there are matching values:

where x between 1 and 9

My recommendation is to only use this column with in or =:

where indicator.month in ('January', 'February', 'March', 'April')

Upvotes: 2

Related Questions