How to Fix ‘Conversion failed when converting date and time’ in SQL Server

The “Conversion failed when converting date and/or time from character string” error in SQL Server appears when SQL Server cannot interpret a text value as a valid date or datetime. This usually happens when the input string uses the wrong format, contains an impossible calendar value, or is interpreted differently because of session settings such as SET LANGUAGE or SET DATEFORMAT.

In simple terms, SQL Server is telling you that the string you passed does not match the date format it expected. The safest fix is to use an unambiguous date format, apply the correct CONVERT style only when needed, and store date values in a real date type such as date or datetime2 instead of varchar.

Why This Error Happens?

This error is usually triggered by one of the following:

• a regional date string is interpreted in the wrong order, such as mm/dd/yyyy vs dd/mm/yyyy
• the value contains an invalid date, such as 2026-02-29
• the query relies on session settings like SET DATEFORMAT or SET LANGUAGE
• dates are stored as text instead of real date types
• dirty data contains extra spaces, special characters, or mixed formats

According to Microsoft’s SQL Server documentation, character-string-to-date conversion can be nondeterministic when language and date-format settings vary. For that reason, ISO 8601 is usually the safest format for string literals. For reference, see Microsoft’s guidance on nondeterministic date literal conversion
and the SET DATEFORMAT documentation.

1. Use ISO 8601 Dates Whenever Possible

If you control the input format, use ISO 8601 strings such as yyyy-MM-ddTHH:mm:ss. This format is much safer because SQL Server interprets it consistently, regardless of session language or date format settings.

DECLARE @date_time_value varchar(30) = '2019-03-28T21:02:04';

SELECT CONVERT(datetime2(7), @date_time_value, 126) AS parsed_date_time;

For SQL Server date and time conversions, Microsoft’s CAST and CONVERT style reference and datetime2 documentation are the best references to follow.

2. Match the Input String to the Correct CONVERT Style

If the source data is already stored in a regional text format, you need to convert it using the matching style code. For example, style 103 expects the British/French order dd/mm/yyyy.

Wrong Format

The following value fails because the string is written in mm/dd/yyyy order, but style 103 expects dd/mm/yyyy.

DECLARE @date_time_value varchar(100) = '10/16/2015 21:02:04';

SELECT CONVERT(datetime2, @date_time_value, 103) AS UK_Date_Time_Style;

Correct Format

Style 103 works only when the input string follows dd/mm/yyyy. If your input uses a two-digit year, style 3 is the matching version for dd/mm/yy.

DECLARE @date_time_value varchar(100) = '16/10/2015 21:02:04';

SELECT CONVERT(datetime2, @date_time_value, 103) AS UK_Date_Time_Style;

Commonly used styles include:

• 101 = mm/dd/yyyy
• 103 = dd/mm/yyyy
• 112 = yyyymmdd
• 126 = ISO 8601 yyyy-MM-ddTHH:mm:ss

Use style-based conversion only when you are forced to deal with legacy string data. If you can choose the format yourself, ISO 8601 is still the better option.

3. Session Settings Can Change How SQL Server Reads the Same String

One of the most confusing parts of this error is that the same input string can work in one session and fail in another. That happens when SET DATEFORMAT or SET LANGUAGE changes how SQL Server interprets the order of month, day, and year.

DECLARE @input varchar(30) = '12/31/2008 09:01:01';

SET DATEFORMAT dmy;
SELECT TRY_CONVERT(datetime2, @input) AS parsed_with_dmy;

SET DATEFORMAT mdy;
SELECT TRY_CONVERT(datetime2, @input) AS parsed_with_mdy;

If your application runs in different environments, relying on session settings is risky. Use ISO 8601 or pass native date parameters from the application instead of string literals.

4. Check for Impossible Dates

Sometimes the format is correct, but the value itself is invalid. A common example is 29 February in a non-leap year.

Wrong Date

DECLARE @date_time_value varchar(100) = '2019-02-29 21:02:04';

SELECT CAST(@date_time_value AS datetime2) AS date_time_value;

Correct Date

DECLARE @date_time_value varchar(100) = '2019-02-28 21:02:04';

SELECT CAST(@date_time_value AS datetime2) AS date_time_value;

5. Use TRY_CONVERT or TRY_CAST to Find Bad Rows Safely

If your table contains mixed or messy date strings, using CAST or CONVERT can stop the entire query at the first bad value. In those cases, TRY_CONVERT or TRY_CAST is safer because it returns NULL instead of raising an error.

SELECT
    raw_date,
    TRY_CONVERT(datetime2(7), raw_date, 126) AS parsed_date
FROM dbo.Events;

You can also use it to isolate invalid rows:

SELECT raw_date
FROM dbo.Events
WHERE TRY_CONVERT(datetime2(7), raw_date, 126) IS NULL
  AND raw_date IS NOT NULL;

If your source format is regional, replace style 126 with the correct style for that input, such as 103 for dd/mm/yyyy.

For Microsoft’s official behavior, see TRY_CONVERT and TRY_CAST.

6. Store Dates as DATE or DATETIME2, Not as Text

The best long-term fix is structural: do not store dates in varchar, nvarchar, char, or text columns. Store them in date, datetime, or preferably datetime2, then format them only when displaying them to users.

This avoids ambiguous parsing, reduces conversion errors, and keeps filtering, sorting, indexing, and comparisons more reliable. For formatting output, Microsoft notes that FORMAT() is meant for locale-aware display strings, while CAST and CONVERT should be used for general type conversion.

ISO 8601 Example

If you need a string format that is safe across regions and environments, ISO 8601 is the best default choice.

DECLARE @date_time_value varchar(100) = '2019-03-28T21:02:04';

SELECT CONVERT(datetime2, @date_time_value, 126) AS [yyyy-MM-ddTHH:mm:ss];

Best Practices to Avoid This Error

• Prefer ISO 8601 date strings for literals and imports.
• Use the correct CONVERT style only when working with regional legacy text data.
• Use TRY_CONVERT or TRY_CAST when validating imported rows.
• Do not rely on SET LANGUAGE or SET DATEFORMAT unless you fully control the session.
• Store date values in date or datetime2 columns, not text columns.
• Pass strongly typed date parameters from application code whenever possible.