SQL Server Error 241

Error number

241

Severity

16 (user-correctable error)

State

1

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 241 means SQL Server tried to read a piece of text as a date or time and the text is not a date it can recognise, such as N/A, an empty word, or a date that does not exist.

Whenever text meets a date or time type, SQL Server parses the text: in an explicit CAST or CONVERT, when inserting text into a date column, when comparing a text column with a date, or when adding text to a datetime value. If the text does not match any date format the engine accepts, the statement stops with 241 at severity 16. Unlike conversion errors for numbers, the message does not quote the bad value, so you have to find the row yourself.

Error 241 means the text could not be parsed at all. Its close relative, error 242, means the text was read as a date but one of the parts is out of range, for example month 31. Which of the two you get can depend on the target type: '2026-02-30' raises 241 when converted to date or datetime2, but 242 when converted to datetime.

Common causes

• Placeholder text in a varchar column that is meant to hold dates: N/A, unknown, - or TBC.
• Dates that do not exist, such as 30 February, converted to date or datetime2.
• Joining text to a datetime with +: datetime has higher data type precedence, so SQL Server tries to turn the text into a date.
• Formats SQL Server does not parse, such as 23.10.2026 14h30 or month names in a language other than the session language.
• A text column compared with a date literal or a date column in a WHERE clause, so every row is converted, including bad ones.

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 order_ref, order_date
FROM dbo.Staging
WHERE order_date IS NOT NULL
  AND TRY_CONVERT(date, order_date) IS NULL;

SELECT order_date, COUNT(*) AS row_count
FROM dbo.Staging
WHERE order_date IS NOT NULL
  AND TRY_CONVERT(date, order_date) IS NULL
GROUP BY order_date
ORDER BY row_count DESC;

SELECT c.name AS column_name, t.name AS data_type, c.max_length
FROM sys.columns AS c
JOIN sys.types AS t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID(N'dbo.Staging');

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.

INSERT INTO dbo.Orders (order_ref, order_date)
SELECT order_ref, TRY_CONVERT(date, order_date)
FROM dbo.Staging;

SELECT CONVERT(date, '23/10/2026', 103) AS uk_style,
       CONVERT(date, '10/23/2026', 101) AS us_style,
       CONVERT(date, '23.10.2026', 104) AS german_style;

DECLARE @ordered_at datetime = '2026-10-23T14:30:00';
SELECT CONCAT('Ordered on ', CONVERT(varchar(19), @ordered_at, 120)) AS label;

UPDATE dbo.Staging SET order_date = NULL
WHERE TRY_CONVERT(date, order_date) IS NULL;

ALTER TABLE dbo.Staging ALTER COLUMN order_date date NULL;

Examples

Loading text dates that include a placeholder

SET NOCOUNT ON;
CREATE TABLE dbo.Staging (order_ref INT, order_date VARCHAR(20));
INSERT INTO dbo.Staging VALUES (1, '2026-10-01'), (2, 'N/A'), (3, '2026-10-05');
CREATE TABLE dbo.Orders (order_ref INT, order_date DATE);

INSERT INTO dbo.Orders (order_ref, order_date)
SELECT order_ref, order_date FROM dbo.Staging;   -- 'N/A' is not a date

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

A date that does not exist

SELECT CAST('2026-02-30' AS date) AS d;   -- February has no 30th

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

Adding text to a datetime with +

DECLARE @ordered_at datetime = '2026-10-23T14:30:00';
SELECT 'Ordered on ' + @ordered_at AS label;   -- SQL Server tries to read 'Ordered on ' as a date

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

How to prevent it

Store dates in date, datetime2 or datetimeoffset columns, never in text. When data arrives as text (files, APIs, staging tables), validate it with TRY_CONVERT at the boundary and decide explicitly what happens to values that fail.

Send dates from applications as typed parameters, not strings, and use ISO 8601 text (2026-10-23 or 2026-10-23T14:30:00) in scripts. See SQL data types explained for choosing the right date type.

Common questions

Learn more on CodeWithSQL

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

Official documentation

• CAST and CONVERT (Transact-SQL)
• TRY_CONVERT (Transact-SQL)
• Data type precedence (Transact-SQL)
• Database Engine events and errors 0 to 999

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.