How can we read data as a predefined date format?

Edit Subject

Hello Bart,

I hope I understand the question correctly. I suppose you need to read a date value from a text file, but DQ Analyzer decides the column containing date data is of type string.

In that case you need to edit file metadata manually with Metadata editor. You can open the Metadata editor from the context menu on your file in Explorer choosing Edit metadata action. In the Metadata editor switch the Type of the column you want to day (or datetime if it contains time values as well).
Click into the Format field for the column and open the format dialog by clicking the button on the right. Choose the appropriate date format from the drop down list, or choose other and write your own.

The date format is using patterns from Java Date Format - d for day, y for year, M for month etc. The patterns are case sensitive (common mistake is writing down 'm' for month, but it means minute).

Once you write down the right format, close the dialog and the Metadata editor and profile the file by Create profile action or any other way you normally do. Now DQ Analyzer should profile the column as a date value.

I am attacching the screenshot with Metadata editor set up for several date and datetime formats.

I understand setting these values is a bit more complex then doing anything else in DQ Analyzer, so feel free to ask furtherly how to use Metadata editor or so or provide me with an example of format of your date, so I can help you with writing down the right date format.