3

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

enter image description here

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) >= CAST(CONVERT(VARCHAR(10), StartDate, 101) AS DATETIME) 
    AND 
    CAST(CONVERT(VARCHAR(10), Createdate, 101) AS DATETIME) <= 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:

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

    CONVERT(VARCHAR(10), Createdate, 101)  >= CONVERT(VARCHAR(10), StartDate, 101)  
    AND 
    CONVERT(VARCHAR(10), Createdate, 101) <= 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?

  2. I changed language of user 'Two' to English by following steps Security->logins->User'two'->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?

closed as off-topic by Max Vernon, Philᵀᴹ, dezso, RolandoMySQLDBA, Mark Sinkinson Sep 30 '15 at 16:12

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, Philᵀᴹ, dezso, RolandoMySQLDBA, Mark Sinkinson
If this question can be reworded to fit the rules in the help center, please edit the question.

15

The condition that is responsible for the error:

CAST(CONVERT(VARCHAR(10), createdate, 101) AS DATETIME) >= 
CAST(CONVERT(VARCHAR(10), STARTDATE, 101) AS DATETIME) 
AND 
CAST(CONVERT(VARCHAR(10), createdate, 101) AS DATETIME) <= 
CAST(CONVERT(VARCHAR(10), ENDDATE, 101) AS DATETIME)

is trying to do this comparison:

CAST(createdate AS DATE) >= CAST(STARTDATE AS DATE)
AND 
CAST(createdate AS DATE) <= 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). "Sane" 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) >= 
CONVERT(VARCHAR(10), STARTDATE,  120) 
AND 
CONVERT(VARCHAR(10), createdate, 120) <= 
CONVERT(VARCHAR(10), ENDDATE,    120)


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


-- style code 112 (ISO):   yyyymmdd
CONVERT(VARCHAR(8), createdate, 112) >= 
CONVERT(VARCHAR(8), STARTDATE,  112) 
AND 
CONVERT(VARCHAR(8), createdate, 112) <= 
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) 
  >= DATEADD(day, DATEDIFF(day, 0, STARTDATE), 0) 
AND 
DATEADD(day, DATEDIFF(day, 0, createdate), 0) 
  <= DATEADD(day, DATEDIFF(day, 0, ENDDATE), 0)

or simplified to (possibly use indexes):

createdate >= DATEADD(day, DATEDIFF(day, 0, STARTDATE), 0) 
AND 
DATEADD(day, DATEDIFF(day, 0, createdate), 0) <= 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) >= CAST(STARTDATE AS DATE)
AND 
CAST(createdate AS DATE) <= CAST(ENDDATE AS DATE)

or even better, simplify to (possibly use indexes):

createdate >= CAST(STARTDATE AS DATE)
AND 
CAST(createdate AS DATE) <= 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.

  • one more question, when we change language settings following steps Security->logins->User'two'->Default Language do i need to reload objects using any special way? because appending Set Language to any script show effects on execution but first way doesn't. – AA.SC Oct 1 '15 at 11:36
  • 1
    Please don't get me wrong, your question makes sense, but you are now asking about something very different from the issue you have asked about in the beginning ("The conversion of a varchar data type to a datetime data type resulted in an out-of-range value"). Please don't make a mess of your question and this answer and keep the focus on the issue at hand. If you have a different question, even if related to the current problem, please create a new thread for it. – Andriy M Oct 1 '15 at 13:23
  • Andriy both issues relate to each other, actually previously we were running application with English and when we have switched to British English our SPs started crashing. And I am just digging how things work – AA.SC Oct 1 '15 at 13:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.