SQL Server Error 242

Error number

242

Severity

16 (user-correctable error)

State

3

Product

Microsoft SQL Server Database Engine

Versions

All supported SQL Server versions (2016 to 2025), Azure SQL Database, Azure SQL Managed Instance

Category

Data Type & Conversion

Last reviewed

23 October 2026

What it means

Error 242 means SQL Server read the text as a date but one part of it is out of range for the target type, most often because the day and month were read in the wrong order (31/12 read as month 31).

To turn text such as 31/12/2026 into a datetime, SQL Server has to decide which number is the day and which is the month. It uses the session setting DATEFORMAT, which comes from the session language: us_english reads mdy, british reads dmy. When the order is wrong, a day above 12 becomes an impossible month and the conversion fails with 242 at severity 16. The same text can therefore work for one login and fail for another.

The two type names in the message are the source text type and the target type. 242 also appears when the date is valid but outside the target range: datetime starts at 1753-01-01 and smalldatetime runs only from 1900-01-01 to 2079-06-06. Text that is not a date at all, such as N/A, raises error 241 instead.

Common causes

• Text in dd/mm/yyyy form converted while the session language is us_english (DATEFORMAT mdy), or mm/dd/yyyy text under a dmy language.
• A login or application whose default language differs from the one the code was tested with, for example a service account set to British.
• The format yyyy-mm-dd converted to datetime under a dmy language: for datetime and smalldatetime this format is not language neutral and is read as yyyy-dd-mm.
• A date before 1753 (for datetime) or outside 1900 to 2079 (for smalldatetime), often a placeholder such as 0001-01-01 or 9999-12-31.
• An impossible day such as 30 February converted to datetime.

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

SELECT @@LANGUAGE AS session_language, date_format
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

SELECT name, type_desc, default_language_name
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G')
  AND name NOT LIKE '##%'
ORDER BY default_language_name, name;

SELECT order_date,
       TRY_CONVERT(datetime, order_date)      AS session_format,
       TRY_CONVERT(datetime, order_date, 103) AS as_dmy,
       TRY_CONVERT(datetime, order_date, 101) AS as_mdy
FROM dbo.Staging
WHERE TRY_CONVERT(datetime, order_date) IS NULL;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

SELECT CAST('20261231' AS datetime)            AS basic_iso,
       CAST('2026-12-31T00:00:00' AS datetime) AS iso_with_t;

SELECT CONVERT(datetime, '31/12/2026', 103)     AS uk_text,
       TRY_CONVERT(datetime, '31/12/2026', 101) AS us_style_returns_null;

ALTER TABLE dbo.Orders ALTER COLUMN order_date datetime2(0) NOT NULL;

Examples

The same text under DATEFORMAT dmy and mdy

SET NOCOUNT ON;
SET DATEFORMAT mdy;   -- us_english default
SELECT CAST('31/12/2026' AS datetime) AS read_as_mdy;   -- month 31 does not exist
SET DATEFORMAT dmy;   -- British and many other languages
SELECT CAST('31/12/2026' AS datetime) AS read_as_dmy;   -- 31 December 2026

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

read_as_dmy
-----------------------
2026-12-31 00:00:00.000

yyyy-mm-dd is not safe for datetime under DATEFORMAT dmy

SET NOCOUNT ON;
SET DATEFORMAT dmy;   -- what SET LANGUAGE British gives you
SELECT CAST('2026-12-31' AS datetime) AS as_datetime;  -- read as yyyy-dd-mm: month 31
SELECT CAST('2026-12-31' AS date) AS as_date;          -- works: date always reads yyyy-mm-dd

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

as_date
----------
2026-12-31

A year before the datetime range

SELECT CAST('1700-01-01' AS datetime) AS d;   -- datetime starts at 1753-01-01

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.

How to prevent it

Write date literals in scripts as yyyymmdd or yyyy-mm-ddThh:mm:ss, prefer date and datetime2 for new columns, and send dates from code as typed parameters. Then neither the login language nor a SET DATEFORMAT in some other script can change the result.

Test with a login whose default language differs from yours (for example British against us_english); it catches day and month mix-ups before production does. More on date handling: CONVERT() and CAST().

Common questions

Learn more on CodeWithSQL

• CONVERT()
• CAST()
• SQL data types explained

Official documentation

• SET DATEFORMAT (Transact-SQL)
• SET LANGUAGE (Transact-SQL)
• datetime (Transact-SQL)
• CAST and CONVERT: date and time styles

Last technically reviewed 23 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.