Determine the Date Format Style and/or Type

Frank Bryant
Frank Bryant used Ask the Experts™
on
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(DATE,[TheColumnEDate],105),101)

... 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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Nope, as text files do not have any 'metadata' that defines the data type / style of columns, such as an .xsd file would have for XML.

So this forces the developer to ass-u-me the data type.

Who's the source of the 'text dumps being received lately'?   If it's all in your power, I'd reach out and touch them to tell them to quit changing the definition of the files without contacting you previously.

Author

Commented:
jimhorn,

Thanks, and you confirmed my "assumption"; unfortunately I am in the position to use "as-is".
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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'.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial