MS SQL Server Invalid Dates / Valid Date Range

Date formats supported by Microsoft SQL Server and DataSelf data warehouses that are built on it.

Microsoft SQL Serve only accepts valid calendar dates for datetime data types 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.

• Invalid calendar dates such as Feb 29 (unless in leap years) or April 31.

Addressing Invalid Dates from Source Data

Transforming invalid dates from source data into valid dates in the data warehouse.

Option 1: No-code fix: ETL+ Force Varchar(max)

• On the ETL page, select the target table with invalid dates.

• Click Design on the right panel.

  • For every column with potential invalid dates, check the Force checkbox. This turns the field into a varchar(max) in the temp table extraction process, thus accepting all values.

  • Set the Dw Data Type back to date, or datetime, or datetime2. ETL+ creates a TRY_CONVERT function which turns invalid dates rows into NULL.

Click here for more details.

Option 2: Low-code fix: Custom ETL+ Extract SQL

Use ETL+ Extract SQL transformations to avoid or transform invalid dates. This requires knowledge of the source system syntax.

• E.g.: For Providex tables with invalid dates WHERE DateField >= {d'1990-01-01'}.

Option 3: 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.

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

Related Pages

• T-SQL Date Expressions – date expressions & functions.