How to SELECT between two dates in SQL Server

Question

I need to give a select count in a table using the range of two dates, however my dates are in "yyyy-MM-dd" format, and when I execute the select SQL it returns the following error:

Converting a varchar data type to a datetime data type resulted in a value out of range

Here is the query I'm using:

SELECT COUNT(*) AS score 
FROM requests 
WHERE date_em BETWEEN '2019-04-01' AND '2019-04-30'

Table structure

• date_em = DATETIME

Can someone help me?

Answer 1

My preferred method is:

SELECT COUNT(*) AS score
FROM requests
WHERE date_em >= '2019-04-01' AND
      date_em < '2019-05-01'

This handles the time component and will make use of an index.

The following also works well in SQL Server:

SELECT COUNT(*) AS score
FROM requests
WHERE CONVERT(date, date_em) BETWEEN '2019-04-01' AND '2019-04-30'

In SQL Server, this will also use an index (if available). However, normally functions on columns preclude the use of an index, so I'm more hesitant about this approach.