Date Format Error
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Error Message
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.