SQL 2008 R2: Msg 241 converting datetime from character string - by samot-dwarf

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 570387 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 6/24/2010 8:35:24 AM
Access Restriction Public

Description

If I run a the following statement I get the message 241 from the date at '2011/31/01 00:00:00' (the second order attribute). The other dates (same format) are converted correct, even if I replace the "wrong" date with one of the others.

If I run the same statement at SQL 2005 or set the compatibility of my database to SQL 2005 the statement works fine. Also it works if I add a CAST() or change the date format to 'yyyymmdd'.

I agree, the statement looks wired, but I have to use a similar (a little more complex) to get the required result.

SELECT Id, FromDate, ToDate
  FROM tbl
 WHERE ISNULL(ToDate, '2999/31/12 23:59:59') >= '2010/01/06 00:00:00'
 ORDER BY ABS(DATEDIFF(day, '2005/01/02 00:00:00', ISNULL(FromDate, '1899/31/12 00:00:00'))),         
          ABS(DATEDIFF(day, '2011/31/01 00:00:00', ISNULL(ToDate,  '2999/31/12 23:59:59'))),    
          ISNULL(FromDate, '1899/31/12 00:00:00') DESC
Sign in to post a comment.
Posted by samot-dwarf on 7/27/2010 at 11:13 AM
The main purpose for my bug report is not the fact, that you changed the format (this is ok for me), but that all 7 dates at my statement are in the YDM-format and only the 5th causes an error, while the other 6 are translated correct (even if I swap f.e. the 5th and the 3rd date (both inside of DateDiff)).

If I would get the error for all dates it would ok, but for only one date seems to be an error in some of your analysing-routines, which should be investigated.

best regards
Thomas

PS: of course I changed my application meanwhile.
Posted by Umachandar [MSFT] on 7/27/2010 at 10:53 AM
Hi,
Thanks for your feedback. The behavior you are seeing is by design. Due to the introduction of the new date/time types in SQL Server 2008, we had modify the type inference behavior i.e., how literal values are interpreted. As a result of this change, it affects date/time related functions that take strings as input. The functions that are affected are datediff, datename, and datepart which convert the string literal to datetimeoffset value/type. Converting string to datetimeoffset doesn't recognize the YDM format and that is why you see the conversion failure. The following Books Online topic http://msdn.microsoft.com/en-us/library/bb675168.aspx has a note to this effect:

The YDM date format is not supported when converting from a string format to date, time, datetime2, or datetimeoffset.

But we need to make an explicit note in the the datediff documentation that datetiff now parses strings as datetimeoffset. This has no impact on the result if the string is a datetime, but this means YDM no longer supported without explicit cast.

The best practice is to use language neutral string formats (ISO formats for example) so you don't rely on the language setting. For presentation purpose, you can always format the string on the client-side or in your queries if necessary. Hope this clarifies the behavior.

--
Umachandar, SQL Programmability Team