Filter by dates in sql server

Use the operator between. If you need to pass dates to SQL as literals, use the format YYYYMMDDthat, in my experience, always works, regardless of the language/culture the SQL Server server and client are set to, in short, to see the data from the first semester of 2016, the query would be something similar to:

select *
  from miTabla
 where Fecha between '20160101' and '20160630';

If the fields are DateTime type, you might want to include time information, in that case, continuing with the example of using literals, you can use the format YYYYMMDD HH24:MM:SS, for example:

select *
  from miTabla
 where Fecha between '20160101 00:00:00' and '20160630 23:59:59';

The recommendation is, whenever possible, to pass date values ​​as parameters, but the final syntax will depend on the capabilities of the data connection layer of your programming language.

Personally, the pattern that I always use for this type of query, and that handles well if the dates in your database have a time component, is the following:

-- simular parámetros con fechas de inicio y fin.
declare @fechaInicio date = '2014-05-05';
declare @fechaFin date = '2014-05-06';

select * from tbl
 where campoFecha >= @fechaInicio
   and campoFecha < dateadd(day, 1, @fechaFin);

---

More explanation...

If the column campofechahas a time component, obviously the filter with:

where campoFecha between '2014-05-05' and '2014-05-06'

... is incorrect, because it is equivalent to:

where campoFecha >= '2014-05-05'
  and campofecha <= '2014-05-06'

...and this would exclude dates like 2014-05-06 12:00:00.

It is true that you can express the condition as suggested in the accepted answer:

where campoFecha between '2014-05-05' and '2014-05-06 23:59:59'

... but on the one hand, this doesn't seem very clean to me. On the other hand, depending on where you define the parameters, it may be a little weird that you have to define a time component, and it may not be possible to do so depending on the type you use.

But more importantly, in theory that condition is also flawed. For example, in SQL Server, the type datetimecan also include fractions of seconds. So it is possible, though unlikely, that you have a date 2014-05-06 23:59:59.500that is excluded by the query.

It is for this reason that I prefer to use an inclusive condition for the start date and an exclusive condition for the end date:

where campoFecha >= '2014-05-05'
  and campofecha < '2014-05-07'

This is much more accurate and cannot fail. The only downside is that it doesn't use the operator betweenthat many seem to like.

But in this regard, I leave a link to an article written by Aaron Bertrand, a recognized SQL Server guru, who argues that the use of betweenis dangerous for these types of conditions: What do BETWEEN and the devil have in common? .

SELECT *
FROM tabla
WHERE CampoFecha 
BETWEEN '2011/02/25' AND '2011/02/27'

SELECT *
FROM tabla 
WHERE CampoFecha >= '2011/02/25' 
AND CampoFecha <= '2011/02/27'

I would do it in the following way:

SELECT *
FROM tabla
WHERE CampoFecha 
BETWEEN :fechaInicio AND :fechaFin ;

It is ideal, since in normal use you would not enter the dates directly in the table, but through the application, where you would format them as a date, with the mask that suits you best (YYMMDD, DD-MM-YYYY, etc. )

Using WHERE in this case (as stated in another answer) is not productive, because for that there is already BETWEEN.