It's a date!

I've started using the date related functions in the data quality developer tool. I've found some fun ways to implement them and wanted to share.

Is_Date

Before you use any date function you need to be sure you're dealing with a date string. The Is_Date function, available in the Expression transform, is how you test a string for proper date format. The syntax is simple, Is_Date(Input Port). The result is 1 if the string is indeed a date and 0 if it is not a date string. Refer to the graphic below for an illustration of how to implement the Is_Date function.



To_Date

If the result of the IsDate test is a negative one, you'll need to convert the string to a date to use the other date functions. In order to do this you'll need to use the To_Date function. Again the syntax is fairly simple, To_Date(Input Port, 'DATE FORMAT'). Refer to the graphic below for an illustration of how to implement the To_Date function.



Date_Diff

Now that you know you have date strings to analyze you can move on to more advanced date functions. In many cases, the time period between dates is a critical metric when performing root cause analysis. For example, there are regulations surrounding financial securities settlements. A useful application of the date difference function could be a process that sends an alert when the time period approaches the mandated settlement deadline.

Essentially this process will measure the difference between two dates (in days) and, if the difference is equal to the mandated difference less one day, it will send an alert to the appropriate people. Refer to the process flow below for an illustration of the logic.



Analyzing the difference between two dates is performed by using the DateDiff function available in the Expression transform. The syntax is, again, fairly straightforward. Date_Diff(Input Date1, Input Date2, 'TIME DIFFERENCE FORMAT'). It is critical to remember that the second date is subtracted from the first date and the difference is returned. The time difference format to measure in days is expressed as 'DD'. Refer to the graphic below for an illustration of how to implement the date difference function.



These are just a few date functions available in Informatica Data Quality Developer. However, it is easy to see how they can be implemented to provide critical services to the business. I also recommend using the date compare function to check the chronolical order of dates.
Until next time ... have fun dating!