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:

![Columns][1] [1]: https://i.sstatic.net/CzBXU.png

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

Thank you very much.

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 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:

![Columns][1] [1]: https://i.sstatic.net/CzBXU.png

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 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:

![Columns][1] [1]: https://i.sstatic.net/CzBXU.png

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

Thank you very much.

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:

![Columns][1] [1]: https://i.sstatic.net/CzBXU.png

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

Thank you very much.

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');