MS SQL Server and Invalid Dates / Valid Date Range

SQL Server does not accept invalid dates.

It only accepts valid calendar dates between 01/01/1753 and 12/31/9999 and NULLs.

Therefore:

  • Months must be between 1 and 12.

  • Days must be between 1 and 31.

  • Years between 1753 and 9999.

  • It won’t accept invalid calendar dates such as Feb 29 (unless in leap years) or April 31.

Using Excel to Find/Fixing Invalid Dates

This is possible if you can export the source system data directly to Excel or CSV, and then edit the records with bad dates in the source system:

  • Export the source table(s) with date issues to CSV or Excel.

  • Open the file in Excel.

  • Mark the column(s) that have dates.

  • Go to Date ribbon → Data tools, Data Validation → Data Validation. Configure it with something like the following image. Click again Data Validation → Circle Invalid Data.

  • Go through the Excel, find invalid dates, and fix their values directly in the source system.

Keywords: valid date range, invalid dates, data integrity issues, dirty data, data cleaning, valid datetime, invalid datetime.