Conversion failed when converting date and/or time.

Applies to: SQL Server 2008 R2.
 

Problem Description.
 

The following exception can be found on the ring buffers of SQL Server, and specifically on the RING_BUFFER_EXCEPTION:

Msg 241, Level 16, State 1, Line 7
Conversion failed when converting date and/or time from character string.

Cause

This exception may be originated when you are using a trying to enter a date on a different format than the format specified on the regional settings of the computer, or you are using a date format that is dependent of the DATEFORMAT or the language of the login is different from the instance default.

Another possible cause may be an expected implicit conversion from a date stored on a string format to one of the new date/time types with larger precision introduced on SQL Server 2008, like datetime2 and datetimeoffset.

One more possible reason is the date stored in string format that you are trying to convert to a date format is not a valid date.

Solutions/Workarounds.


These are a few solutions and workarounds for this exception:

1. Try to use the ISO 8601 formats that are the only international standards to handle dates, and are not dependent of the computer locale, the operating system language, the default language of the login and not dependent of the DATEFORMAT. The two ISO 8601 formats are: '2015-12-24T16:41:16' and '2015-12-24T16:41:16-04:00'.
2. Try not to use text boxes to capture dates and do not define date parameters for stored procedures (for example) as string data types.
3. Try using the IS_DATE, or try using PARSE and TRY_PARSE as explained here. They were introduced on SQL Server 2012.
4. .NET Framework provides the DateTime.TryParseExact and DateTime.TryParse methods.
5. If you are using Visual Basic with SQL Server, the IsDate function may help.
6. If you are using Microsoft Access with SQL Server, you can try the IsDate function.

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.