I'm gathering data from several sources of which I have no control (including external vendors) which all have a common DATE column.
The problem is, most of these systems have VARCHAR(50) fields and thus have widely varying formats for the date. Is there any functionality built into DQS, MDS or SSIS that will help me normalize the dates into a true datetime column in my warehouse
or do I have to program some mad TSQL myself?
D April 13, 2013 4:59pm
E 2013/04/13 16:59:18.999
and so on...Seems like date matching/normalizing would be pretty common...is this a DQS, SSIS or MDS function? Or straight up SQL...
You can use the Data Conversion component in SSIS to easily convert the varied date values in the source data column to a standardized date format. For more information various date-related data types in SSIS, see
Integration Service Data Types.
DQS does provide you the ability to format the output of a Date to standardize the output. For more information, see
However, you can map your source values from an Excel file or SQL Server table/view in DQS. Further, to map the source data to a DQS domain for performing data-quality activities,
the source data type must be supported in DQS, and must match with the DQS domain data type. If your source data column is of a data type other than the supported date type, you cannot map it to the
Date type domain in DQS. For information about the supported data types for DQS operations, see
(SQL Server Documentation | Twitter:
NOTE: Please remember to appropriately vote a post as "helpful" or mark as "answer" to help the community.
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.