How do I fix a string error with a date?

kim_barragan0126

I am getting the below error and now sure how to fix. I run this data weekly, so I am assuming I have bad data somewhere.

ColemenWilson

Where is your data coming from? If you can edit the value in the source system directly, you could change it from '0/00/2024' to a proper date. If that isn't an option, you could fix the data using a formula tile:

CASE WHEN `DateField` = '0/00/2024' THEN '12/31/2024' ELSE `DateField` END

MichelleH

@kim_barragan0126 Based on your screenshot, it looks like your source data is from Excel. Likely what's happening is that you have a typo in one of your date columns. Since you only have 5 date columns, I'd suggest opening the filters of each date column to look for invalid date values. If a value is invalid, Excel cannot group it under a particular month/year with expandable "+" signs, which makes it easy to filter to bad values and correct them in the sheet before uploading.

Alternatively, you can try converting your datatypes in the ETL using the TRY_CAST function in a formula tile instead of the Alter columns. When using TRY_CAST, if it encounters a value that it cannot convert to a different datatype then the output for that row will be NULL instead of causing the dataflow to fail. Your formulas should look like this:

TRY_CAST(`Best Few Date` as DATE())

ColemenWilson

Where is your data coming from? If you can edit the value in the source system directly, you could change it from '0/00/2024' to a proper date. If that isn't an option, you could fix the data using a formula tile:

CASE WHEN `DateField` = '0/00/2024' THEN '12/31/2024' ELSE `DateField` END

kim_barragan0126

I have five date fields so I am not sure the one that has the issue. All the dates are noted as the below.

Is there a way to determine which field is causing the issue?

ColemenWilson

If you can change the type, the 2012-03-14 format is preferred.

In Domo, navigate to the dataset and then inspect the schema. You'll be able to see the data type for each field. If you see a field that should be a date, but shows 'abc Text' Then that is likely where your issue is.

kim_barragan0126

It shows Date in my data, but string when I run it through ETL. I tried using a Alter tile and change to Date, but that causes an error. I also tried changing the file itself to text and date and both issues. Maybe I need support ticket?

ColemenWilson

Huh. So as the input it is a date, but when using it in the ETL it is converting it to a text and not allowing you to convert it back to date? Seems like you have identified which date field is causing the issue, did you find the value '0/00/2024' in that column?

MichelleH

@kim_barragan0126 Based on your screenshot, it looks like your source data is from Excel. Likely what's happening is that you have a typo in one of your date columns. Since you only have 5 date columns, I'd suggest opening the filters of each date column to look for invalid date values. If a value is invalid, Excel cannot group it under a particular month/year with expandable "+" signs, which makes it easy to filter to bad values and correct them in the sheet before uploading.

Alternatively, you can try converting your datatypes in the ETL using the TRY_CAST function in a formula tile instead of the Alter columns. When using TRY_CAST, if it encounters a value that it cannot convert to a different datatype then the output for that row will be NULL instead of causing the dataflow to fail. Your formulas should look like this:

TRY_CAST(`Best Few Date` as DATE())

kim_barragan0126

Thank you all! I have fixed the issue, not sure how I fixed it, but it is good now. I will go back now and see what steps worked. I appreciate all the help as it did help me determine what the issue was, and how to research it. Thank you!