|
|
Error Message
-- SQL Server 2005, 2008 and beyond
Server: Msg 210, Level 16, State 1, Line 1
Conversion failed when converting datetime from binary /
varbinary string.
-- SQL Server 2000
Server: Msg 210, Level 16, State 1, Line 1
Syntax error converting datetime from binary /
varbinary string.
|
Causes
This error occurs when converting, either implicitly or explicitly using the CAST or CONVERT functions, a VARBINARY value to a DATETIME or SMALLDATETIME value or in the case of SQL Server 2008, to a DATE, TIME, DATETIME2 or DATETIMEOFFSET value, but the original value stored in the VARBINARY data type was not of DATETIME or SMALLDATETIME data type but of a VARCHAR data type.
To illustrate, here’s how the error can occur using an implicit conversion of a VARBINARY data type to a DATETIME data type:
DECLARE @VarBinary VarBinary(10)
DECLARE @DateTime DATETIME
SET @VarBinary = CAST('1998/01/24' AS VARBINARY(10))
SET @DateTime = @VarBinary
Msg 210, Level 16, State 1, Line 5
Conversion failed when converting datetime from binary/varbinary string.
The error was generated because the date value stored in the VARBINARY variable was of a VARCHAR data type and not of a DATETIME data type even though the intent was to store a date value.
Here’s another way of encountering this error using an explicit conversion of a VARBINARY data type value to a SMALLDATETIME data type value:
DECLARE @VarBinary VarBinary(10)
SET @VarBinary = CAST('1998/01/24' AS VARBINARY(10))
SELECT CAST(@VarBinary AS SMALLDATETIME)
Msg 210, Level 16, State 1, Line 4
Conversion failed when converting datetime from binary/varbinary string.
A different error message will be generated if the same scripts above are executed in SQL Server 2000:
DECLARE @VarBinary VarBinary(10)
DECLARE @DateTime DATETIME
SET @VarBinary = CAST('1998/01/24' AS VARBINARY(10))
SET @DateTime = @VarBinary
Msg 210, Level 16, State 1, Line 5
Syntax error converting datetime from binary/varbinary string.
DECLARE @VarBinary VarBinary(10)
SET @VarBinary = CAST('1998/01/24' AS VARBINARY(10))
SELECT CAST(@VarBinary AS SMALLDATETIME)
Msg 210, Level 16, State 1, Line 4
Syntax error converting datetime from binary/varbinary string.
Solution / Work Around:
There are a couple of ways of overcoming this error. The first method is to use the correct data type when storing a value to a VARBINARY variable. In this case, the value to be stored in a VARBINARY variable or column has first to be converted to a DATETIME or SMALLDATETIME data type so that when it is implicitly converted back to a DATETIME or SMALLDATETIME value, this error will not be generated.
To illustrate, here’s an updated version of the script earlier that will avoid this error:
DECLARE @VarBinary VarBinary(10)
DECLARE @DateTime DATETIME
SET @VarBinary = CAST(CAST('1998/01/24' AS DATETIME) AS VARBINARY(10))
SET @DateTime = @VarBinary
The only difference between this script from the one above is the conversion of the date string value into a DATETIME data type using the CAST function before converting the value into a VARBINARY data type.
Similarly using the second script, here’s how the script will look that will avoid this error from happening:
DECLARE @VarBinary VarBinary(10)
SET @VarBinary = CAST(CAST('1998/01/24' AS SMALLDATETIME) AS VARBINARY(10))
SELECT CAST(@VarBinary AS SMALLDATETIME) AS [SmallDateTime]
SmallDateTime
---------------------
1998-01-24 00:00:00
The second method of overcoming this error is to first convert the output of the VARBINARY data type into a VARCHAR data type then convert it to a DATETIME data type afterwards.
To illustrate, here’s how both original scripts will look like to avoid this error:
DECLARE @VarBinary VarBinary(10)
DECLARE @DateTime DATETIME
SET @VarBinary = CAST('1998/01/24' AS VARBINARY(10))
SET @DateTime = CAST(@VarBinary AS VARCHAR(10))
DECLARE @VarBinary VarBinary(10)
SET @VarBinary = CAST('1998/01/24' AS VARBINARY(10))
SELECT CAST(CAST(@VarBinary AS VARCHAR(10)) AS SMALLDATETIME)
SmallDateTime
---------------------
1998-01-24 00:00:00
|