Error converting data type varchar to datetime. - by Crakdkorn

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.

Sign in
to vote
ID 573675 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 7/7/2010 2:49:35 PM
Access Restriction Public


When the CSLA object is called, it receives a value from VS 2010 (a datetime) that is formatted with 7 places to the right of the decimal point causing the above error:

exec [dbo].[resnet_csla_LoginLog_Insert] @MemberID=11111,@LoginTypeID=16,@LoginTS='2010-07-07 17:01:43.4670000',@IPAddress=N'',@LoginName=N'estrelita.m.jones',@Comments=N'CID EJ(ID: 302658) Assumed By CID EJ(ID: 11111) Other: /core/resnet/memberedit.aspxid=11111',@SessionID=N'aa1009d4-7a36-4075-bb2c-0f5a6b2cf15a'
Sign in to post a comment.
Posted by Robert Heinig II on 6/10/2014 at 5:32 AM
For future readers: The example has another pitfall: It uses a near-ISO literal format that breaks on non-US 2012 instances. Always include the 'T' (and dummy time even if not needed) when the receiving type is datetime (or when it's not clearly documented that datetime2 semantics are reliably applied).
Posted by Microsoft on 7/15/2010 at 1:49 PM
The datetime type allows a maximum of 3 digits of precision after the millisecond decimal point -- for more information, see ('Supported String Literal Formats for datetime'). In the case of '2010-07-07 17:01:43.4670000', 7 digits of decimal precision is too many to represent as a datetime, and hence no implicit conversion will be performed (this is by design).

As an alternative, consider using the datetime2 type instead, which supports 7 digits of millisecond precision -- see Further, use of datetime2 is recommended for all future work because (in addition to providing better precision) it aligns with the SQL Standard and is therefore more portable.