Querying using between with sql server 2005

Question

Querying using between with sql server 2005

Navigation

#1 by (1 votes)

0

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

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 want 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'

The two queries 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 otherwise do it?

sql sql-server

asked by anonymous 04.02.2015 / 18:17

1 answer

Dynamic Update in component Primefaces through autocomplete Sort by date

score 1 · Accepted Answer

The problem is that you are comparing the Date type with the Datetime type. The second parameter, in terms of Datetime , is considered as 04/02/2015 00:00:00.000 , which is the start of the day. For the query to include all day, do so:

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