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)
Solved! Go to Solution.
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:
Hope this helps,
Arnaldo