242 SQL Origin: Data OSI layer 7 · Application Last updated 18 Nov 2026

SQL Server Msg 242: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

A date string couldn't be converted because it's invalid or in the wrong format for the session language.

Exact message you may see The conversion of a varchar data type to a datetime data type resulted in an out-of-range valueout-of-range value datetime SQL ServerSqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999datetime out of range
Seen on: SQL Server SQL

Meaning

Use ISO YYYYMMDD or YYYY-MM-DDTHH:MM:SS, parameterise dates as datetime2, and avoid depending on SET DATEFORMAT/language. datetime ranges from 1753; datetime2 from 0001.

Common causes

  • dd/mm/yyyy read as mm/dd/yyyy
  • Invalid date such as 31 April
  • Date before 1753 for datetime
  • Empty string converted to date
  • Wrong DATEFORMAT/language

⚡ Quick fix

  1. Use ISO 8601 / YYYYMMDD literals
  2. Use datetime2 and typed parameters
  3. Validate with TRY_CONVERT

Detailed fix by platform

SQL

  1. SELECT TRY_CONVERT(datetime2, '2026-13-45') AS ok_or_null;

How to diagnose

  1. Format — ISO?
  2. Type — datetime2?
  3. Value — Valid date?

🔧 Still not fixed?

Many errors look alike. If the steps above didn’t solve it, one of these is probably what you’re facing:

Where this error sits

Origin: Data. Errors that are often confused with or caused by this one (click to open):

100038 — Snowflake: Numeric value 'X' is not recognized (SQL)100038209 — SQL Server Msg 209: Ambiguous column name (SQL)209245 — SQL Server: Conversion failed when converting the varchar value 'x' to data type int. (Msg 245) (SQL)2455123 — SQL Server Msg 5123: CREATE FILE encountered operating system error 5 (Access is denied) while attempting to open the physical file (SQL)5123515 — SQL Server Msg 515: Cannot insert the value NULL into column 'X', table 'Y'; column does not allow nulls (SQL)5158114 — SQL Server Msg 8114: Error converting data type varchar to numeric (SQL)8114CASE — CASE expression returns mixed types: conversion failed / types cannot be matched (SQL)CASEdate format — Dates mixed up: dd/mm/yyyy vs mm/dd/yyyy, ISO 8601 recommended (SQL)date format242

Explore the map of this error Full error map →

🧠 Still stuck? Analyze your error

Paste the full message, response headers or stack trace — we'll detect the platform and point to the most likely cause.

💬 What worked for others

No tips yet — did something else fix this for you?

Share what worked for you

Tips are checked by an editor before they appear. Links are removed.