DateTime Tool Conversion Error

Question

Hi,

Could anyone assist me on how to resolve the date time conversion errors I am receiving for only a couple of data sets? The dates are formatted differently, and although I can replace "." with "/", I am uncertain how I can get all these dates to align. Some are dd/m/yyyy while the rest are m/dd/yyyy.

(I am new)

datasample.yxmd

Sample_FY2011.csv

ArnaldoSandoval · Accepted Answer

Hi @soulton

Your workflow's cleansing section should be stronger specially regarding DataTime fields, if you look at DateTime tool messages, it is telling you why those errors are happening:

There are a bunch of records having the Transaction Date with a format dd.M.yyyy, there are several ways to go about handling this situation, like shown below:

Here the data stream was split in two (because we are trying to figure out the reason for the error), we added a filter to the second data stream selecting records not having dots in their Transaction Date field, and the DateTime tools reported to error message, also, this filter (its F output) tolds as that the ConverPosted field is also formatted with dots instead of slashes.

Now that we know what's going on, we have different ways to handle the issue, one if replacing dots with slashes, or adding datetime tools to handle the different date format; you should notice that the date format for those dates with dots is dd.M.yyyy so you may need two datetime tools or a fancy formula to align your date formats.

Probably the simpliest approach is adding two DateTime tools, as so:

* I kept the analysis logic in the workflow, you should remove them once you understand why it was happening, producing a clean workflow.
* This solution is not 100% robust, as it handles M/dd/yyyy and dd.M.yyyy date formats with Transaction Date and Convert Posted sharing the same date format in the same record, it does not include other date formats like yyyy/M/dd or yyyy.M.dd; your workflow cleansing section should be able to handle all posibilities depending on your data, keep that in mind 🙂

Hope this helps,

Arnaldo

binu_acs · Accepted Answer

@soulton Another way of doing this with the Multi-Field formula