how to use between in sql server

Question

ID          NAME                     TIME
---------------------------------------------
1   United Arab Emirates    3:00
2   Sri Lanka                     2:00
3   Turkey                    4:00
4   Yemen                     4:00
5   Us                    11:00
6   Sweden                    5:00
7   England                   6:00
8   Singapore           23:00

Dear I want list of name between 2.00 to 10.00 time which is varchar field. i did

select * from tblindiantime where IndianTime  between ('1.00') and ('10.00')

which gives me wrong records.. in other cases it shows me conversion erros.. plz help

Answer 1

You need to specify '1.00' and 10.00' as datetimes, assuming the IndianTime column is a datetime.

Answer 2

Try this:

... where convert(int, substring(IndianTime, 0, charindex('.', IndianTime))

This might be off by 1 in either way, but you get an idea.

Answer 3

What data type is your time stored as? *CHAR? If your RDBMS is MSSQL and you are using the SQL 2008 time datatype, this should work

select * from tblindiantime  where IndianTime between '01:00' and '10:00'