The conversion of a varchar data type to a datetime data type resulted in an out-of-range value I have two users in my database. When I execute following query under user ONE it successfully executes; however when I execute it under user TWO it generates a error message:

Query:

select cast ('2015-04-28 16:00:00.000' as datetime)


Error Msg:


  Msg 242, Level 16, State 3, Line 11
  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.


User ONE has English as default language and its a simple Server login
User TWO has British English as default language and is also used as Application User   

DB Properties



Since in my real query columns are in Where clause to get result set for a specified date range:

    CAST(CONVERT(VARCHAR(10), Createdate, 101) AS DATETIME) &gt;= CAST(CONVERT(VARCHAR(10), StartDate, 101) AS DATETIME) 
    AND 
    CAST(CONVERT(VARCHAR(10), Createdate, 101) AS DATETIME) &lt;= CAST(CONVERT(VARCHAR(10), EndDate, 101) AS DATETIME)


I verified columns Createdate, StartDate and EndDate have a DateTime datatype in database.

What I have tried so far:


Removed casting cast(columnname as datetime) and its working fine.

CONVERT(VARCHAR(10), Createdate, 101)  &gt;= CONVERT(VARCHAR(10), StartDate, 101)  
AND 
CONVERT(VARCHAR(10), Createdate, 101) &lt;= CONVERT(VARCHAR(10), EndDate, 101) 


This is not the solution there are more than 3 thousand SPs and we can't change all in a single click? is there any other solution?
I changed language of user 'Two' to English by following steps Security-&gt;logins-&gt;User'two'-&gt;Default Language to verify is this the only reason but query still failed on Casting 


Any idea what settings are we missing? Do I have to verify date format for both users?  

If removal of casting or use of T is the only solution then it require time for modifications and then QA, do we have any temporary solution so nothing effect with our deadline and we have enough time to change SPs according to best practices?
The condition that is responsible for the error:
CAST(CONVERT(VARCHAR(10), createdate, 101) AS DATETIME) &gt;= 
CAST(CONVERT(VARCHAR(10), STARTDATE, 101) AS DATETIME) 
AND 
CAST(CONVERT(VARCHAR(10), createdate, 101) AS DATETIME) &lt;= 
CAST(CONVERT(VARCHAR(10), ENDDATE, 101) AS DATETIME)

is trying to do this comparison:
CAST(createdate AS DATE) &gt;= CAST(STARTDATE AS DATE)
AND 
CAST(createdate AS DATE) &lt;= CAST(ENDDATE AS DATE)

but is using the 101 style code (US, mm/dd/yyyy) to first convert the datetime to varchar(10), effectively chopping off the time part and then converting back to datetime - but without explicitly specifying* a style code for the second cast.
Frankly, I'm surprised that it worked at all. It's quite possible that the various queries and procedures that use such conversions are producing false results, depending on server or client settings. (i.e. converting '2015-12-08 16:00:00' (2015-Dec-08) to '12/08/2015' and then to '2015-08-12 00:00:00' (2015-Aug-12)).
What you can do is to change these conversions to not depend on the client settings. The easiest would be to remove the second cast. But that would also require that  you change the 1st cast to use a sane format (and the responding style code). &quot;Sane&quot; meaning a format that preserves the order of dates, i.e. style codes 102, 112, 120, 126. (Do not use styles 101 or 103 that do not preserve order. That would make the issue much worse.) Examples of codes to use:
-- style code 120 (ODBC canonical):   yyyy-mm-dd hh:mi:ss(24h)
CONVERT(VARCHAR(10), createdate, 120) &gt;= 
CONVERT(VARCHAR(10), STARTDATE,  120) 
AND 
CONVERT(VARCHAR(10), createdate, 120) &lt;= 
CONVERT(VARCHAR(10), ENDDATE,    120)


-- style code 126 (ISO8601):   yyyy-mm-ddThh:mi:ss.mmm (no spaces)
CONVERT(VARCHAR(10), createdate, 126) &gt;= 
CONVERT(VARCHAR(10), STARTDATE,  126) 
AND 
CONVERT(VARCHAR(10), createdate, 126) &lt;= 
CONVERT(VARCHAR(10), ENDDATE,    126)


-- style code 112 (ISO):   yyyymmdd
CONVERT(VARCHAR(8), createdate, 112) &gt;= 
CONVERT(VARCHAR(8), STARTDATE,  112) 
AND 
CONVERT(VARCHAR(8), createdate, 112) &lt;= 
CONVERT(VARCHAR(8), ENDDATE,    112)

But the best in my opinion is not to convert to varchar at all and remove the time part using another way. If you are on 2005 and no DATE type is available, you can use this method to strip the time from a DATETIME:
DATEADD(day, DATEDIFF(day, 0, createdate), 0) 
  &gt;= DATEADD(day, DATEDIFF(day, 0, STARTDATE), 0) 
AND 
DATEADD(day, DATEDIFF(day, 0, createdate), 0) 
  &lt;= DATEADD(day, DATEDIFF(day, 0, ENDDATE), 0)

or simplified to (possibly use indexes):
createdate &gt;= DATEADD(day, DATEDIFF(day, 0, STARTDATE), 0) 
AND 
DATEADD(day, DATEDIFF(day, 0, createdate), 0) &lt;= ENDDATE

And finally the best  (and assuming all these columns are DATETIME) and that your version of SQL Server is 2008 or later (and so has the DATE type) is to change these conversions to cast to DATE so they are self-explanatory of the intention, to either:
CAST(createdate AS DATE) &gt;= CAST(STARTDATE AS DATE)
AND 
CAST(createdate AS DATE) &lt;= CAST(ENDDATE AS DATE)

or even better, simplify to (possibly use indexes):
createdate &gt;= CAST(STARTDATE AS DATE)
AND 
CAST(createdate AS DATE) &lt;= ENDDATE 


What happens when a char or varchar value is implicitly converted to a date/time type, is nicely explained by Aaron Bertrand in the blog post Bad habits to kick : mis-handling date / range queries:

.., the only truly safe formats for date/time literals in SQL Server, at least for DATETIME and SMALLDATETIME, are:

YYYYMMDD
YYYY-MM-DDThh:mm:ss[.nnn]



About the question, why a user gets these errors while another user doesn't, this (thank you Paul White) technet page: Using Date and Time Data, explains:

Using Date and Time Formats
String literal formats affect the presentation of data in applications to users but not the underlying integer storage format in SQL Server. However, SQL Server might interpret a date value in a string literal format, input by an application or user for storage or to a date function, as different dates. The interpretation depends on the combination of string literal format, data type, and runtime SET DATEFORMAT, SET LANGUAGE and default language option settings.