Home Dashboard Directory Help

Error converting data type varchar to datetime. by Crakdkorn


 as By Design Help for as By Design

Sign in
to vote
Type: Bug
ID: 573675
Opened: 7/7/2010 2:49:35 PM
Access Restriction: Public
User(s) can reproduce this bug


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 http://msdn.microsoft.com/en-us/library/ms187819.aspx ('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 http://msdn.microsoft.com/en-us/library/bb677335.aspx. 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.
Sign in to post a workaround.
Posted by Crakdkorn on 7/7/2010 at 2:51 PM
Force the value into a template of 3 digits to the right of the decimal place only.