Determine the Date Format Style and/or Type
I have encountered an interesting situation; the text dumps being received lately have mixed date format styles/types in it; i.e. Column E Date Format is "22991231" (YYYYMMDD) and the Column S Date Format is "12/31/2299" (MM/DD/YYYY).
The YYYYMMDD format is what is expected for all date columns, as the Date Validation process runs the following code ...
CONVERT(VARCHAR,CONVERT(DA
... and the date data YYYYMMDD is converted to MM/DD/YYYY.
However the Date Validation Process Fails (Conversion failed when converting date and/or time from character string.) when the Column S Data is encountered.
Is there a way to identify the Date Format Style/Type and skip the specific date columns that do not need to be converted?
The YYYYMMDD format is what is expected for all date columns, as the Date Validation process runs the following code ...
CONVERT(VARCHAR,CONVERT(DA
... and the date data YYYYMMDD is converted to MM/DD/YYYY.
However the Date Validation Process Fails (Conversion failed when converting date and/or time from character string.) when the Column S Data is encountered.
Is there a way to identify the Date Format Style/Type and skip the specific date columns that do not need to be converted?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Good luck.
I always use the line when estimating ETL projects, 'The more guarantees you can make about the data source, the lower my estimates are'.
I always use the line when estimating ETL projects, 'The more guarantees you can make about the data source, the lower my estimates are'.
ASKER
Thanks, and you confirmed my "assumption"; unfortunately I am in the position to use "as-is".