[FIXED] Cannot coerce String (11/17/1993) to Date

Issue

Feel like I've tried literally every single permutation of as Date format, as String as Date, as String, etc. What on earth am I doing wrong?

Trying to feed data into SF from a CSV. Simple birthday field. Comes in as:

10/29/2003 in an Excel column.

Keep getting this error:

Cannot coerce String (11/17/1993) to Date, caused by: Text '11/17/1993' 

Date_of_Birth__c: $."Date of Birth" as Date {format: "YYYY-M-dd" }

Also tried..

$."Date of Birth" as Date as String{format : "YYYY-DD-MM"}

Can anyone help me figure out why I cannot seemingly turn a simple string format around?

Solution

You are using a format that doesn't match the input data. Clearly the date is using format month-day-year. You are using format year-month-date or year-date-month which fails to parse the date. Additionally you are using incorrect formatting patterns. DataWeave uses Java patterns for parsing/formatting dates. The meaning of those patters is different for uppercase and lowercase characters. You should almost always use dd (day of month), MM (month of year) and yyyy (year). Using DD (day of year) and YYYY (week based year) will not work for normal dates.

Instead try using the right format: {format: "MM-dd-yyyy" }

Answered By - aled
Answer Checked By - Mildred Charles (JavaFixing Admin)