Conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I am trying to run a simple query to get all rows created in November:

SELECT COUNT(*)
FROM dbo.profile 
WHERE [Created] BETWEEN '2014-11-01 00:00:00.000' 
AND '2014-11-30 23:59:59.997';

SMSS returns:


The conversion of a varchar data type to a datetime data type resulted
in an out-of-range value.

I do not understand why the data is being converted from varchar to datetime when 'Created' is set to datetime:

Do I need to tell the server that 'Created' is datetime? If not, why am I getting this varchar message?

Edit: The value in the database was YYYY-MM-DD. Reply from @SqlZim below says that I need to use convert() to tell sql what format the date is in the db - and to replace the space character with the letter T:

select count(*) 
from dbo.profile 
where [created] between convert(datetime,'2014-11-01T00:00:00.000') 
and convert(datetime,'2014-11-30T23:59:59.997');`

I checked your profile and saw that you are in the UK. If your sql server is set to use the dateformat dmy then that explains your issue. Without using the 'T' instead of the space in the datetime string, Sql Server won't recognize it as ISO8601 format.

Try this:

select count(*) 
  from dbo.profile 
  where [created] between convert(datetime,'2014-11-01T00:00:00.000') 
                      and convert(datetime,'2014-11-30T23:59:59.997');

Querying using dates and/or datetimes can be tricky, to make sure you are getting what you are looking for I recommend reading:

• Aaron Bertrand's Bad habits to kick : mis-handling date / range queries

• Robyn Page's SQL Server DATE/TIME Workbench

edit: to clarify the out of range value in your error message would be from interpreting the month as 30 and the day as 11.

StackExchange Database Administrators Q#86313, answer score: 9