Query using between with sql server 2005

Question

I’m making a query using the operator Between to select a date range, in the SQL Server 2005 database. Below are the table data Servico:

linha1: 2015-02-04 14:51:01.577
linha2: 2015-02-04 14:51:02.137
linha3: 2015-02-04 14:51:04.810
linha4: 2015-02-04 00:00:00.000
linha5: 2015-02-04 00:00:00.000
linha6: 2015-02-04 00:00:00.000
linha7: 2015-01-01 00:00:00.000
linha8: 2015-01-20 00:00:00.000
linha9: 2015-01-20 10:20:44.000
linha10: 2015-01-20 10:20:44.200

I intend to select the range '20/01/2015' to '04/02/2015' with the queries:

select * from Servico
WHERE convert(datetime,dataServico,103) between '04/01/2015' and '04/02/2015'

ou

select * from Servico
WHERE convert(datetime,dataServico,103) >= '04/01/2015' and 
      convert(datetime,dataServico,103) <= '04/02/2015'

Both darlings return the data below:

linha4: 2015-02-04 00:00:00.000
linha5: 2015-02-04 00:00:00.000
linha6: 2015-02-04 00:00:00.000
linha8: 2015-01-20 00:00:00.000
linha9: 2015-01-20 10:20:44.000
linha10: 2015-01-20 10:20:44.200

The big problem is that it does not return lines 1,2 and 3. How to solve this query or do otherwise?

Answer 1

The problem is you’re comparing the type Date with the guy Datetime. The second parameter in terms of Datetime, is regarded as 04/02/2015 00:00:00.000, which is the beginning of the day. So that the consultation includes the whole day do so:

select * from Servico
WHERE convert(datetime,dataServico,103) between '04/01/2015' and '04/02/2015 23:59:59'