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.

Addressing Invalid Dates in your SQL Data Warehousing

Option 1: Fix the Data in your Source System

Ideally, your source system shouldn’t have bad data such as invalid dates - they are usually mistakes or not important data points. Having discrepancies between in your source system and BI solution is not a best practice.

The approach described here is to find the invalid dates using Excel. 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.

Option 2: Fix the data in the Data Warehouse

Here are some options to use ETL+ to address bad dates:

1. Use ETL+ Extract SQL Statement transformations to avoid or transform the ingesting of invalid dates. E.g., use the following clause for Providex tables with invalid dates WHERE DateField >= {d'1990-01-01'}.

2. Use ETL+ Design page to force the field into varchar(max). Click here for details.

3. Use ETL+ Design page to force the field into varchar(max) and convert it back to date or datetime. Click here for details.

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