Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Date Format Error

quiroz
5 - Atom

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.

 

quiroz_0-1644252174899.png

 

quiroz_1-1644252174916.png

 

quiroz_2-1644252174927.png

 

quiroz_3-1644252174941.png

 

 

 

 

Any help will be appreciated.

 

Thank you.

Rafael

 

7 REPLIES 7
DavidSkaife
13 - Pulsar

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.

 

DavidSkaife_0-1644263310801.png

 

Edit: Changed formula to match the MMDDYYYY format originally requested

 

binuacs
21 - Polaris

@quiroz 

binuacs_0-1644255766592.png

 

atcodedog05
22 - Nova
22 - Nova

Hi @quiroz 

 

My take on this.

Workflow:

atcodedog05_0-1644259059867.png

 

Hope this helps : )

 

quiroz
5 - Atom

Hi David,

 

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

 

quiroz_1-1644265473602.png 

quiroz_2-1644265525921.png

 

 

 

Any thoughts?

 

DavidSkaife
13 - Pulsar

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
5 - Atom

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. 

quiroz_0-1644272271594.png

quiroz_1-1644272373996.png

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
5 - Atom

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. 

Labels
Top Solution Authors