Home Dashboard Directory Help

Conversion failed when converting date and/or time from character string. by Mehmet GÜZEL


Status: 

Closed
 as By Design Help for as By Design


4
0
Sign in
to vote
Type: Bug
ID: 355655
Opened: 7/10/2008 12:58:48 AM
Access Restriction: Public
1
Workaround(s)
view
5
User(s) can reproduce this bug

Description

When I run this SQL, I have an error that is 'Conversion failed when converting date and/or time from character string.'.

SELECT CAST('2008-08-08 11:11:11.6730000' AS DATETIME) -- conversion error


SELECT CAST(CAST('2008-08-08 11:11:11.6730000' AS DATETIME2) AS DATETIME) --regular converting

If implicit conversion to DATETIME2 should be supported.



Details
Sign in to post a comment.
Posted by SAKUTE1686 on 12/9/2010 at 11:42 PM
When I run below SQL, I have an error that is 'Conversion failed when converting date and/or time from character string.'.

DATEADD(ss,( ( Hours * 60 * 60 )+ (OvertimeHours * 60 * 60 ) ),TransactionDate) -- conversion error

Please suggest any other way to remove this error...
Posted by AaronBertrand on 10/3/2009 at 1:55 PM
This behavior does not seem consistent. Explicit CONVERT is typically able to discard any information that is not deemed relevant because it is too precise. For example, I can convert a DATETIME value to SMALLDATETIME this way:

SELECT CAST('2008-08-08 11:11:11.673' AS SMALLDATETIME);

The seconds and fractional seconds are discarded. Why can't the same happen when going from a high precision type (DATETIME2) to a lower one (DATETIME)? I noticed as well that *implicit* conversion works fine, for example:

DECLARE @c TABLE (d DATETIME);
INSERT @c SELECT sysdatetime();
SELECT d FROM @c;

Interesting...
Posted by Microsoft on 7/10/2008 at 6:04 PM
Thanks for the feedback.

Although we're introducing the new date/time types with larger precision (up to 100ns) support in SQL 2008, we didn't change the behavior for the existing datetime/smalldatetime types mainly due to back-compat reasons. As one of the criteria for us to introduce any new feature, we're very restricted on creating any breaking change to existing functionalities.

Having said that, what you've found is an expected behavior same as what's designed in SQL 2005.

thanks
Posted by Microsoft on 7/10/2008 at 6:04 PM
Thanks for the feedback.

Although we're introducing the new date/time types with larger precision (up to 100ns) support in SQL 2008, we didn't change the behavior for the existing datetime/smalldatetime types mainly due to back-compat reasons. As one of the criteria for us to introduce any new feature, we're very restricted on creating any breaking change to existing functionalities.

Having said that, what you've found is an expected behavior same as what's designed in SQL 2005.

thanks
Sign in to post a workaround.
Posted by SAKUTE1686 on 12/9/2010 at 7:59 AM
When I run this SQL, I have an error that is 'Conversion failed when converting date and/or time from character string.'.

DATEADD(ss,( ( [Hours * 60 * 60 )
                         + (OvertimeHours * 60 * 60 ) ),
                        TransactionDate) AS OFFTime