CODEWITHSUNDEEP
mysqlt-sql
dpchimmili
dpchimmili

Reputation: 221

Between and is working fine in MySql but not in T-SQL

We had a project with backend MySQL which we recently migrated to SQL Server 2005. A query like the one given below used to work fine when MySQL was used where as it is giving unexpected result.

select * from employees where joindate between '2013-05-01' and '2013-05-01'

here the starting and ending dates are same.

MySQL used to give the records of employees who joined on '2013-05-01'. Where as T-SQL is giving the records of employees who joined on '2013-04-30'

joindate column in the table has time part as well in which we are recording the exact time when the employee joined along with date.

Is there any work around for this problem. Please kindy let me know.

thanks, dpchimmili.

Upvotes: 2

Views: 90

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

You can use BETWEEN with TSQL if you just cast the value to a DATE.

select * from employees 
where CONVERT(DATE, joindate) 
between '2013-05-01' and '2013-05-01'

Better though is to check if the date is less than the next date, since that avoids a calculation per row and will hit indexes better;

select * from employees 
where joindate>='2013-05-01' 
  and joindate<DATEADD(day,1,'2013-05-01')

An SQLfiddle to test with.

Upvotes: 2

Joel Harkes
Joel Harkes

Reputation: 11681

MySQL used to give the records of employees who joined on '2013-05-01'. Where as T-SQL is giving the records of employees who joined on '2013-04-30'

that is how the different servers implemented the between function differently: for as mySQL includes all dates between and with the same date, T-sql only includes the dates between those dates and not the dates itself.

use Where statements as work around WHERE joindate >='2013-05-01' and joindate <= '2013-05-01' or jsut WHERE joindate ='2013-05-01'

Upvotes: 2

Related Questions