Date is not a Date

[TeamTJ]

I am pulling data from a SQL Server database and have a field called Date. It is a VarChar50 field.

I am needing to convert it to a Date field, but I am getting this error when using the select tool to convert it:

Date: "05/01/2019" is not a valid Date

It throws this error for every line, but those are all valid dates.

Suggestions?

[JordanB · Accepted solution]

@TeamTJ  you can use the datetime tool in the parse category to format the field into an Alteryx Date format YYYY-MM-dd

Best,

Jordan

[TeamTJ]

Thank you!!

[SergioD]

You can also cast that field as date, from your sql syntax.

see cast function for more information:

https://www.1keydata.com/sql/sql-cast.html

[nicho_x86]

I'm having the same issue but with the date format dd-mmm-yyyy (e.g. 01-Apr-2020).

Even using the parse date feature I'm unable to get this recognised as a date.

I have tried shortening the year to yy and triming the field and this also doesn't work. Any ideas?

**EDIT

I've found that the date parse tool doesn't support dates in this format and I had to do a replace on all months to numbers before I could convert.

Any chance the date parse functionality can be updated to work with date formats that have a month abbreviation in them or has this already happened? - I'm still using 2018.4!

Thanks

Nick

[DataMeister]

Sergio: I appreciate your suggestion, but the reason we use Alteryx is to avoid coding whenever possible, and this situation is a perfect example of how the app should understand what a date is. It's not rocket science, but knowledge workers encounter this stuff every day so it needs to be automated.

[DataMeister]

Jordan: I just ran into this same date problem with Excel data, and tried your suggestion to no avail. I'm disappointed that Alteryx hasn't grabbed the date bull by the horns--because it's a recurring problem for workers all over the world. Please tell the developers that it may not be glamorous, but we all know what dates are and that they have various formats. Alteryx should see that a column called "Date" is full of dates, and can ask how we want them formatted, besides asking whether the month comes first or second, because that's a typical difference. Similarly, a column called Lat or Latitude is going to be exactly that--I wish Tableau had brains to see that one too! Alteryx's Select tool lets us tell it that what it first sees as a string should be a ... date. As seen in this thread, that setting doeesn't work as promised--but the remedy shouldn't be another tool or SQL, but a smarter Select tool with inherent knowledge of date handling. For myself right now, I'm giving up on the date in Excel and will leave it as a string, unfortunately. And that's my rant!

[DanTheActuary]

I have a similar issue, but my dates are not in the same format.  I have pulled together 40+ data files using one of the batch imports someone was kind enough to share and all my data fields are being pulled in as V_String.  For the dates it's not as simple as using a formula because there is missing data for some dates and other dates have MM/DD/YYY, YYYY-MM-DD TIME, or YYYY-MM-DD.  Is there someway in Alteryx to fix this or is it easier to do it outside?

[tkbruin]

Dittos. Issues like this make Alteryx unusable as an ETL tool for many. The solution workaround for us is to keep everything as text, and do the heavy lifting in the target environment.

I am so disappointed with Alteryx. I guess it's back to SSIS. Let me know if you know of any other worthy tools.

[DataMeister]

tkbruin: You hit the nail on the head--workarounds don't make for a good application! I wonder what it'll take for Alteryx developers to notice this and several other bedrock problems, and dig in to really fix them. Alteryx has the potential to be the best drag & drop, no-code automation tools in the world, if its developers will follow the yellow brick road.