Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Differences Between ISNULL and COALESCE Functions
Error Messages
Home > SQL Server Error Messages > Msg 210 - Conversion failed when converting datetime from binary/varbinary string.
SQL Server Error Messages - Msg 210 - Conversion failed when converting datetime from binary/varbinary string.

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
Related Articles :