The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

What it means

SQL Server converted a string to a date and time, split it into year, month and day, and got a date that can’t exist (month 31, 30 February, hour 25) or one outside the target type’s range. It stops the statement, and nothing is written.

Msg 242, Level 16, State 3, Server 7732422b7f56, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Its sibling 241 means the text didn’t look like a date to begin with:

Msg 241, Level 16, State 1, Server 7732422b7f56, Line 1
Conversion failed when converting date and/or time from character string.

Two things decide whether a string converts:

• The session’s date order. For datetime and smalldatetime, '2026-12-31' and '31/12/2026' are read in the order set by SET DATEFORMAT, which follows the session’s language: us_english is month-day-year; British, Deutsch and Français are day-month-year. Each login has a default language, so the same statement can work for one login and fail for another. The date, datetime2 and datetimeoffset types always read yyyy-mm-dd as year, month, day.
• The type’s range. datetime covers 1 January 1753 to 31 December 9999, smalldatetime 1 January 1900 to 6 June 2079, and date and datetime2 years 0001 to 9999.

How the same strings came out on our server:

Common causes

1. Day and month swapped. Dates typed or exported as dd/mm/yyyy and read by a us_english session, or yyyy-mm-dd strings sent to a datetime column from a session whose language is British, German or French.
2. A date before 1753 in a datetime column: 0001-01-01 from an application’s “empty” date (in .NET, DateTime.MinValue), or a converted datetime2 value, which gives The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
3. smalldatetime past 6 June 2079, such as a “never expires” date of 2099-12-31.
4. Impossible dates in imported data: 2026-02-30, 0000-00-00 (MySQL’s zero date), a time of 25:00.
5. Dates stored as text in a varchar column and converted on every query, so one bad row breaks the query.

How to fix it

Send dates as dates

Pass values as typed parameters (date, datetime2) from your code instead of building strings. Nothing is parsed, so the language doesn’t matter. For new columns, use date or datetime2: wider range, and yyyy-mm-dd always means what it says.

Write literals that can’t be misread

'20261231' (no separators) and '2026-12-31T10:30:00' (with the T) are read the same way under every language and DATEFORMAT, for every date type.

Say the format with CONVERT

When the text is in a known format, give CONVERT its style number: 103 is dd/mm/yyyy, 101 is mm/dd/yyyy, 126 is ISO 8601.

SELECT CONVERT(date, '31/12/2026', 103) AS uk_date,
       CONVERT(date, '12/31/2026', 101) AS us_date,
       CONVERT(datetime2, '2026-12-31T10:30:00', 126) AS iso_date;

SET DATEFORMAT dmy; at the top of a script also works, for that session only.

Find the rows that won’t convert

TRY_CONVERT returns NULL instead of failing:

SELECT id, shipped
FROM staging.imports
WHERE shipped IS NOT NULL
  AND TRY_CONVERT(date, shipped, 103) IS NULL;

Replace “empty” dates with NULL

Store “no date” as NULL rather than 0001-01-01 or 1900-01-01, or move the column to datetime2 if early dates are real data.

Reproduce it

On SQL Server 2022 (16.0.4295.3) with sqlcmd, as a login whose default language is us_english, each statement in its own batch:

SELECT CAST('31/12/2026' AS datetime);
SELECT CAST('2026-02-30' AS datetime);
SELECT CAST('31/12/2026' AS date);
SELECT CAST('yesterday' AS datetime);
SELECT CAST('2080-01-01' AS smalldatetime);
SELECT CAST(CAST('0001-01-01' AS datetime2) AS datetime);

Msg 242, Level 16, State 3, Server 7732422b7f56, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Msg 242, Level 16, State 3, Server 7732422b7f56, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Msg 241, Level 16, State 1, Server 7732422b7f56, Line 1
Conversion failed when converting date and/or time from character string.
Msg 241, Level 16, State 1, Server 7732422b7f56, Line 1
Conversion failed when converting date and/or time from character string.
Msg 242, Level 16, State 3, Server 7732422b7f56, Line 1
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.
Msg 242, Level 16, State 3, Server 7732422b7f56, Line 1
The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

After SET LANGUAGE British, CAST('2026-12-31' AS datetime) failed with 242, and '20261231', '2026-12-31T00:00:00' and '2026-12-31' as date and datetime2 all gave 31 December 2026. '1752-12-31' as datetime gave 242, '1753-01-01' worked. '0000-00-00' gave 242 as datetime and 241 as date, and '2026-12-31 25:00:00' gave 242. Inserting a datetime2 variable holding 0001-01-01 into a datetime column failed with the datetime2 message above; into a datetime2 column it worked. The TRY_CONVERT query found 2026-02-30 and n/a among four imported rows. SQL Server 2019 and 2025 printed the same messages.

In Inlet

Query tabs show every result set and message a batch returns, so you can run the TRY_CONVERT check in the same batch as the failing statement. When SQL Server refuses, Inlet shows its message with SQL Server error 242, state 3, severity 16 (or 241) and links to this page.