Date Format Error

[quiroz]

Hi Community,

I am trying to format a DOB to MMDDYYYY, but the New_DOB is not formatting correct. I used a FORMULA  left(right([BIRTH_DATE],5),2) + right([BIRTH_DATE],2) + left([BIRTH_DATE],4) before my JOIN and this is the output. The DOB field type is a V_String on my SELECT tool.

 

Any help will be appreciated.

Thank you.

Rafael

[davidskaife · Accepted solution]

Hi @quiroz 

I suggest trying the following: DateTimeFormat([BIRTH_DATE],'%m%d%Y') as the formula, but i notice you have a record in a different format (8/17/2003) so this won't work for that record as it's not a valid date or time.

Edit: Changed formula to match the MMDDYYYY format originally requested

[binuacs]

@quiroz 

[atcodedog05]

Hi @quiroz 

My take on this.

Workflow:

Hope this helps : )

[quiroz]

Hi David,

Thank you for the information on this issue. I noticed that the new DOB is showing as [NULL] after the JOIN.

 

Any thoughts?

[davidskaife]

You're welcome!

Do you have any NULLs in your incoming data stream, on both the left and right inputs? The join tool will join NULL to NULL as well, and display them first. If you do you could try filtering out the NULL fields first before the join.

If not you could upload a copy of the workflow and i could take a look?

[quiroz]

Yes. I have several NULL fields on the main file. Some of these NULL fields were not populated by the user and I am joining the left side with a CB_ID and on the right side with SAT_ID. Also, my other join fields on my JOIN are New Test Date (left) and New Test Date (right), but it looks like there are some records that matched in both New Test Date and New DOB. 

Are the dates that match on both files are because they do not have NULLS?

I am going to check my input file and filter all the NULL fields.

Thank you for your help!

[quiroz]

Thank you all, for the input. I figured out the date format error on the DOB. The DOB was changed and it was not on the same format 2000-03-15. I used the Text to Columns tool and it worked!

Thank you.