Conversion failed when converting date and/or time.
Applies to: SQL Server 2008 R2.
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.
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.
These are a few solutions
and workarounds for this exception:
- 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'.
- Try not to use text boxes to capture dates and do not define date
parameters for stored procedures (for example) as string data types.
- Try using the
or try using PARSE and TRY_PARSE as explained
were introduced on SQL Server 2012.
- .NET Framework provides the
- If you are using Visual Basic with SQL Server, the
IsDate function may help.
- If you are using Microsoft Access with SQL Server, you can try the