Filter by dates in sql server

Use the operator between . If you need to pass the dates to SQL as literals, use the YYYYMMDD format that, in my experience, always works, regardless of the language / culture in which the server and the SQL Server client are configured, in summary, to see the data for the first half 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 information about the time, for that case, following the example of use of literals, you can use the YYYYMMDD HH24:MM:SS format, for example:

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

The recommendation is, whenever possible, to pass the values of dates 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 as follows:

-- 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 campofecha has a component hour, 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 the condition can be expressed as suggested in the accepted response:

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

... but, on the one hand, this does not seem very clean to me. On the other, depending on where you define the parameters, it may be a bit strange that you have to define a time component, and maybe it can not be done depending on the type you use.

But more importantly, in theory that condition also has a flaw. For example, in SQL Server, the type datetime can also include fractions of seconds. So it is possible, although unlikely, that you have a 2014-05-06 23:59:59.500 date that 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 can not fail. The only disadvantage is that it does not use the between operator that apparently many like it.

But in this regard, I leave a link to an article written by Aaron Bertrand, a recognized guru of SQL Server, who argues that the use of between is dangerous for this type 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, if not through the application, where you would format them as date, with the mask that suits you best (YYMMDD, DD-MM- YYYY, etc)

Using WHERE in this case (as they put in another answer) is not productive, because BETWEEN already exists.