Data conversion errors can be a frequent source of frustration when working with databases. And SQL Server is no exception. Such errors can interrupt workflows and lead to inconsistent results. While data conversion errors often happen during explicit conversions, they aren’t unique to this. Oftentimes the error can be due to an implicit conversion.
This article outlines five of the most common data conversion errors and provides practical steps to avoid them.
1. Conversion failed when converting the varchar value 'xyz' to data type int
This error happens when you try to convert a string with non-numeric characters to a numeric data type.
Example:
SELECT CAST('xyz' AS INT);Output:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'xyz' to data type int.
How to Avoid this Error
Here are some ways to avoid this error:
- Check that you’re converting the correct value.
- Use
TRY_CAST()orTRY_CONVERT(), which return NULL on failure. This won’t suddenly make a bad conversion come good, but it will prevent everything from coming to a halt with an error. - Validate data beforehand with a
WHEREclause.
Here’s what happens if we change the above example to use TRY_CAST():
SELECT TRY_CAST('xyz' AS INT);Output:
null
So while our data still wasn’t converted, it didn’t throw an error either. It quietly returned null. This can help us for those times where we need to display multiple columns, and we don’t want an error on one column to bring the whole query to a halt. In such cases, the other columns will return data as usual, regardless of what happens in the troublesome column. Here’s an example of what I mean:
SELECT
TRY_CAST(123 AS INT) AS c1,
TRY_CAST('xyz' AS INT) AS c2,
TRY_CAST(456 AS INT) AS c3;Output:
c1 c2 c3
--- ---- ---
123 null 456
As you can see, the first and last columns returned data as expected, and the middle column returned null.
2. Arithmetic overflow error converting expression to data type int
The arithmetic overflow error occurs when a numeric value exceeds the range of the target data type.
Example:
DECLARE @int_value INT = 32768;
DECLARE @smallint_value SMALLINT = @int_value;Output:
Msg 220, Level 16, State 1, Line 2
Arithmetic overflow error for data type smallint, value = 32768.
Here, I set an INT value to 32768, and then I tried to convert it to a SMALLINT. The problem is that 32768 is a larger value than a SMALLINT can handle. The SMALLINT type can only handle values up to 32767. Therefore, SQL Server had no option but to return an error.
This example is overly simplistic, and you wouldn’t normally set a value to one type, then immediately set it to another type. So this error is more likely to happen in scenarios where you’re working with existing data that happens to have larger values than you’re catering for.
How to Avoid this Error
Choose the right data type from the start (e.g., BIGINT for potentially large numbers) and use TRY_CAST() or TRY_CONVERT() to handle overflows gracefully.
3. String or binary data would be truncated
This error is thrown when you try to insert a string into a database column that is longer than the column’s defined size.
Example:
-- Create a table with a VARCHAR(10) column
CREATE TABLE ErrorTest (
ShortString VARCHAR(10)
);
GO
-- This INSERT statement will now cause the error
INSERT INTO ErrorTest (ShortString)
VALUES ('This is a long string');
GOOutput:
Msg 2628, Level 16, State 1, Line 8
String or binary data would be truncated in table 'demo.dbo.ErrorTest', column 'ShortString'. Truncated value: 'This is a '.
Here, I defined the column as VARCHAR(10) which means it has a maximum length of 10. However, the string is longer than that, and so trying to insert it causes an error.
By the way, now that we’re done with the example, let’s run the following code to remove the table:
DROP TABLE ErrorTest;How to Avoid this Error
The best way to avoid this error is to simply ensure your column sizes are sufficient for the data they’ll hold.
But if you must have column sizes that are smaller than the data that will potentially be inserted into them, you can explicitly truncate the data using functions like LEFT(). Obviously this will only be an option if you’re not concerned about having truncated data in your database.
It’s also possible to override the default behavior of throwing an error. SQL Server has a setting that will allow data to automatically be truncated upon insert instead of having an error thrown. We can do this with the ANSI_WARNINGS setting.
When SET ANSI_WARNINGS is OFF, SQL Server will truncate data that exceeds the length of a VARCHAR or NVARCHAR column and will not issue a warning or an error. The data will simply be cut off at the maximum length of the column.
This setting can be controlled at the session level.
Here’s an example of how the code behaves differently with the setting turned off:
-- Create a table
CREATE TABLE ErrorTest (
ShortString VARCHAR(10)
);
GO
-- Turn off ANSI warnings for the current session
SET ANSI_WARNINGS OFF;
GO
-- This insert will now succeed, but the data will be truncated
INSERT INTO ErrorTest (ShortString)
VALUES ('This is a long string');
GO
-- Check the data in the table
SELECT ShortString FROM ErrorTest;
GOOutput:
This is a
We can see that, although data was inserted, it wasn’t the full string.
Now, just because we can do something doesn’t mean we should. And this is certainly true when it comes to disabling ANSI_WARNINGS. It’s generally not recommended to rely on this behavior. SET ANSI_WARNINGS ON is the default and considered a best practice because it ensures data integrity. Silently truncating data can lead to data loss and unexpected behavior in your applications. It’s better to handle potential truncation explicitly within your code.
But perhaps more importantly, the ANSI_WARNINGS setting also applies to other errors (divide-by-zero, arithmetic overflow errors, warnings related to aggregate functions, etc). So you would need to understand the full impact of this setting before changing it.
With that said, let’s clean up and revert our settings:
-- Clean up
DROP TABLE ErrorTest;
GO
-- It's a good practice to set ANSI_WARNINGS back to ON
SET ANSI_WARNINGS ON;
GO4. The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
This error happens when a string cannot be interpreted as a valid date.
Example:
SELECT CONVERT(DATETIME, 'February 30, 2025');Result:
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Here, we specified the 30th day of February but February doesn’t have that many days.
This error is more likely to occur when we try to convert a valid date that uses a different format to our session’s settings. For example, I also get the error when I run the following code:
SELECT CONVERT(DATETIME, '28/02/2025');Result:
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
In this case my session’s DATEFORMAT option is set to MDY:
DBCC USEROPTIONS;Output:
Set Option Value
----------------------- --------------
textsize 2147483647
language us_english
dateformat mdy
datefirst 7
lock_timeout -1
quoted_identifier SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
isolation level read committed
MDY is the default format for when we set our LANGUAGE to us_english. Changing the language can also change the DATEFORMAT setting implicitly (although we can override this by explicitly setting DATEFORMAT afterwards).
Options for Avoiding this Error
One way to avoid this error is to specify a consistent date format using the CONVERT() function’s style codes. We can provide the style code as a third argument. In our case, we can use style code 103 to deal with our second example:
SELECT CONVERT(DATETIME, '28/02/2025', 103);Result:
2025-02-28 00:00:00.000
The 103 style specifies that the date format is being provided in DD/MM/YYYY format.
Another option is to set our LANGUAGE to one that uses the appropriate DATEFORMAT before performing the conversion:
SET LANGUAGE British;
SELECT CONVERT(DATETIME, '28/02/2025');Result:
2025-02-28 00:00:00.000
Another way to do it is to set the DATEFORMAT directly. This keeps our language settings intact, while only changing the way dates are formatted. To demonstrate this, let’s change our language back to us_english and then explicitly set the DATEFORMAT setting directly:
SET LANGUAGE us_english;
SET DATEFORMAT DMY;
SELECT CONVERT(DATETIME, '28/02/2025');Result:
2025-02-28 00:00:00.000
One potential issue with changing your session’s settings like this is that you could inadvertently mess things up for future tasks if you forget to revert the changes back to your initial setting. So let’s do that now:
SET LANGUAGE us_english;Just by running that single line, the DATEFORMAT value is reset to the default for us_english. In other words, it’s set back to mdy.
And let’s not forget that another option for dealing with the error is to use TRY_CONVERT() to return NULL on invalid dates:
SELECT TRY_CONVERT(DATETIME, 'February 30, 2025');Result:
null
5. Implicit conversion from data type datetime to varchar is not allowed.
This error is common when you try to concatenate a DATETIME value with a VARCHAR string without explicitly converting it first. SQL Server doesn’t know how to combine these two different data types.
Example:
DECLARE @my_date DATETIME = GETDATE();
SELECT 'The current date is: ' + @my_date;Output:
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
SQL Server had a problem with this conversion, but it’s easily fixed.
How to Avoid this Error
Always use CONVERT() or CAST() to explicitly turn the DATETIME into a string before concatenation:
DECLARE @my_date DATETIME = GETDATE();
SELECT 'The current date is: ' + CONVERT(VARCHAR(20), @my_date, 120);Result:
The current date is: 2025-08-07 22:16:58
As always, we can also use TRY_CONVERT() or TRY_CAST() to return null instead of an error. But this might not always be desirable. Sometimes we want to know if there’s a problem so that we can fix it instead of ignoring it.