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

Question

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

Answer 1

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.

Answer 2

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

The literals you are providing for comparison to the Created column are strings. To compare those literals with the datetime column, SQL Server attempts to convert the strings to datetime types, according to the rules of data type precedence. Without explicit information about the format of the strings, SQL Server follows its convoluted rules for interpreting strings as datetimes.

In my view, the neatest way to avoid these types of issues is to be explicit about types. SQL Server provides the CAST and CONVERT functions for this purpose. When working with strings and date/time types, CONVERT is to be preferred because it provides a style parameter to explicitly define the string format.

The question uses strings in ODBC canonical (with milliseconds) format (style 121). Being explicit about the data type and string style results in the following:

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

That said, there are good reasons (as Aaron points out in his answer) to use a half-open range instead of BETWEEN (I use style 120 below just for variety):

SELECT COUNT(*)
FROM dbo.profile 
WHERE
    [Created] >= CONVERT(datetime, '2014-11-01 00:00:00', 120)
    AND [Created] < CONVERT(datetime, '2014-12-01 00:00:00', 120);

Being explicit about types is a very good habit to get into, particularly when dealing with dates and times.

Answer 3

Since BETWEEN is very problematic due to rounding of different date/time types and other problems, and since YYYY-MM-DD is not a safe format without the awkward T, an open-ended range using ISO standard full dates with no separators is a much better approach:

WHERE Created >= '20141101' AND Created < '20141201';

Answer 4

Another alternative, I recommend using ODBC datetime literals. Despite their name they do not require you connect via ODBC. They bypass the usual conversion rules in SQL Server and are always interpreted as a datetime.

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

---

The other supported ODBC datetime literals are D and T as documented here in Books Online. Both return datetime (not date or time), but the syntax is still compact and unambiguous. The fixed formats for the strings are:

Example:

SELECT TOP (1)
    D = {D '2014-12-27'},
    T = {T '14:49:23.789'},
    TS = {TS '2014-12-27 14:49:23.789'};

The T variant returns the time specified on the current day, as reported by the internal-use-only {fn getdateODBC()}: