MS SQL Server and 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 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 for the data warehouse.
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:
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'}.Use ETL+ Design page to force the field into varchar(max). Click here for details.
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.
Related Pages
T-SQL Date Expressions – date expressions & functions.