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?

Date Time

Accepted answers

JordanB

@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

All comments

JordanB

@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!

funmiOdem

I just started using Alteryx (inherited at a new job that I am at), and all I can say is that it is krapp. Fortunately for me, I have been given the green light to switch to something else. I am more familiar with PowerBI, R and Pandas and I am switching to those.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703