// SQL Server error reference

SQL Server error 242: Out-of-range date conversion

A value was converted between date types (usually varchar to datetime) and the result is outside the target type's range or isn't a real date.

Error number
242
Severity
16
Category
Data conversion and truncation
Error message
The conversion of a %ls data type to a %ls data type resulted in an out-of-range value.

What it means

Error 242 means the conversion understood the format but the resulting date can't be stored. A common case is datetime, which only covers 1753-01-01 to 9999-12-31, receiving an earlier date such as 0001-01-01 from an application's default date value. Another is a day or month out of range for the format being used, such as month 13.

Common causes

  • Converting a string like '0001-01-01' or a .NET DateTime.MinValue into datetime, whose minimum is the year 1753.
  • Day and month swapped under the session's DATEFORMAT, so '13/01/2025' is read as month 13.
  • Converting a datetime2 value outside the datetime range into datetime.

How to fix it

  1. Use datetime2, which covers years 1 to 9999 with better precision, for new columns.

    ALTER TABLE dbo.Events ALTER COLUMN OccurredAt datetime2(3) NOT NULL;
  2. Map placeholder dates from applications to NULL instead of storing year 1.

  3. Use ISO strings or an explicit CONVERT style so day and month can't be swapped.

    SELECT CONVERT(datetime, '13/01/2025', 103);

Reproduce it safely

SELECT CAST('0001-01-01' AS datetime);  -- Msg 242

Related errors

More data conversion and truncation errors

The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.

// fewer errors, faster fixes

Work on SQL Server in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.