...
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
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:
...
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 datetimedate time.