BB Date returns Null for Date Field and an extra Null column

[jdodd]

I have four different date fields that I am trying to format into a standard date format using the BB Date macro.  It is working for three of the fields just fine but the fourth field always returns a Null value in the field and returns a new Null column.  The format of the date string coming in is %m/%d/%Y h:mm an example date is 9/30/2008 0:00.  I am using the BB Date macro since the dates can come in multiple formats for this field depending on the file entered.  A few of the other fields that are working are using the exact same input format and are being converted fine. 

Looking through the macro and sub macros I do not see where the Null column is created but there is a lot of levels within this macro along with a lot of tools being used.  Has anyone familiar with the BB Date seen similar behavior and have any hints on how to solve this?

[ivoller]

AM or PM in the date field? See https://community.alteryx.com/t5/Engine-Works-Blog/Introducing-BB-Date-Date-Parsing-for-the-Rest-of-...

Iain

[jdodd]

Thanks for the suggestion but there were no AM or PM in the date field.  I have read that article and look at the various limitations like only having a two digit year but none of those apply to this scenario.  In fact, I have another date field with the exact same format that does work so I am baffled why one would and the other wouldn't.

[ivoller]

You could try restricting the data to find out which records cause the problem. I’m thinking of something like binary chopping of the column that causes the problem by using select records or similar. I suspect that the problem is most likely due to a different format somewhere in the column. If this is not the case, is it possible for you to post the data?

Iain

[BenMoss]

Hi!

I'm not familiar with the macro but just gave it a try and I seem to get a similar problem.

When I add a second date it no longer generates the 2nd NULL field, and my data in the original field has been adjusted accordingly (though after converting it to a 'date' type this row then NULLs out).

It would appear that this type is not supported by the marco. Could you pass over an example that you say is in the same format but works?

Ben

[MarqueeCrew]

Could we go old school on this with the following formula:

DateTimeParse([Field1],"%m/%d/%Y %H:%M")

This should work for dates in the format that you provided as an example.

Cheers,

Mark

[jdodd]

I think I tracked some reason why this is working differently for one date field versus another.  In the other three date fields, the number of times that the date with the 3/23/2018 0:00 format was extremely low (30 out of 44,000 records).  In those cases, the input string was converted to 3/23/2018 00:00 instead of being a null value.  It still isn't a valid date but I had a value and missed seeing these dates due to the low count.  In the date column that is always null, all the date records have the 3/23/2018 0:00 format.  I did see in the write up on this tool that it would sample a subset of the records to find the top formats so I assume since the majority of the other dates were valid it treated all the records differently then when none of the date formats were valid.  Is there any plans to expand the number of possible formats?

[jdodd]

Yes, the DateTimeParse would work for that specific format.  However, we are reading in multiple files that can potentially have different formats each time.  This is the reason we chose the BB Date to begin with.  Otherwise we would essentially have to write our own version of the BB Date macro tailored to the specific set of formats we can expect for these date fields.

[DanM · Accepted solution]

@jdodd,

This tool was created by one of the Alteryx employees and is technically not a macro that is supported by Alteryx, so no update will happen with this tool unless the creator does so on their own. You are welcome to reach out to the creator and ask them if they have plans to update the tool. Otherwise, you are correct that you would have to create a new tool.

Dan

[NeilR]

@jdodd FYI there is a new version of the tool available - I'm curious if it deals with your data any better than the original version...

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Parse-Dates-Easily-with-BB-Date/ta-p/341682