Odtnhj

There is this strange error in sql query.

The query is something like this.

select * from student where dob between '20150820' and '20150828'

But in the database the column of dob is varchar(14) and is in yyyyMMddhhmmss format,Say my data in the row is (20150827142545).If i fire the above query it should not retrive any rows as i have mentioned yyyyMMdd format in the query.But it retrives the row with yesterday date (i.e 20150827112535) and it cannot get the records with today's date (i.e 20150828144532)

Why is this happening??

Thanks for the help in advance

You can try like this:

select * from student 
where convert(date,LEFT(dob,8)) between 
 convert(date'20150820') and convert(date,'20150828'))

Also as others have commented you need to store your date as Date instead of varchar to avoid such problems in future.

As already mentioned you would need to use the correct date type to have between behave properly.

select *
from student 
where convert(date,LEFT(dob,8)) between '20150820' and '20150828'

Sidenote: You don't have to explicitly convert your two dates from text as this will be done implicitly as long as you use an unambiguous date representation, i.e. the ISO standard 'YYYYMMDD' or 'YYYY-MM-DD'. Of course if you're holding the values in variables then use date | datetime datatype

declare @startdate date
declare @enddate date

select *
from student 
where convert(date,LEFT(dob,8)) between @startdate and @enddate

Sidenote 2: Performing the functions on your table dob column would prevent any indexes on that column from being used to their full potential in your execution plan and may result in slower execution, if you can, define the correct data type for the table dob column or use a persistent computed column or materialised view if your performance is a real issue.

Sidenote 3: If you need to maintain the time portion in your data i.e. date and time of birth, use the following to ensure all records are captured;

select *
from student
where
convert(date,LEFT(dob,8)) >= '20150820'
 and convert(date,LEFT(dob,8)) < dateadd(d,1,'20150828')

All you have to do is to convert first the string to date.

select * 
from student 
where dob between convert(date, '20150820') and convert(date, '20150828')

Why is this happening?

The comparison is executed from left to right and the order of characters is determined by the codepage in use.

Sort Order

Sort order specifies the way that data values are sorted, affecting
the results of data comparison. The sorting of data is accomplished
through collations, and it can be optimized using indexes.

https://msdn.microsoft.com/en-us/library/ms143726.aspx

There are problems with between in T-SQL.

But if you want a fast answer convert to date first and use >= <= or even datediff to compare - maybe write a between function yourself if you want the easy use like between and no care about begin and start times ...

• What do BETWEEN and the devil have in common?