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