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