Date normalization using DQS


  • 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?

    For example

    A            04/13/2013
    B            4/13/2013
    C            4/13/13
    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 this a DQS, SSIS or MDS function?  Or straight up SQL...

    Tuesday, July 23, 2013 11:54 PM

All replies

  • Hello,

    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 here.

    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 here.

    (SQL Server Documentation | Twitter: @vivek_msft)

    NOTE: Please remember to appropriately vote a post as "helpful" or mark as "answer" to help the community.

    Tuesday, July 30, 2013 10:04 AM