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
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 .NETDateTime.MinValueintodatetime, 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
datetime2value outside thedatetimerange intodatetime.
How to fix it
-
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;
-
Map placeholder dates from applications to NULL instead of storing year 1.
-
Use ISO strings or an explicit
CONVERTstyle 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
241Conversion failed for date and/or time8115Arithmetic overflow error converting22008PostgreSQL: date/time field value out of range1292MySQL: Truncated incorrect valueORA-01843Oracle: not a valid month
More data conversion and truncation errors
8152String or binary data truncated2628Truncated in table, column245Conversion failed converting a value8114Error converting data type512Subquery returned more than 1 value8134Divide by zero error encountered220Arithmetic overflow error for data type248Conversion overflowed an int column
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.
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.