Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

Date Time Conversion

Highlighted
6 - Meteoroid

Ok, so I have what I think is a simple question but it's about driven me mad at this point.

 

I have data coming into Alteryx in MM/DD/YYYY format. I want to write the output to excel in the same format and format the field as a date as opposed to text. The reason I want the field formatted as a date is that this file gets imported into another system and it requires the field be in date format. Obviously, I could bring the date in as text and output to text and then convert the field to date in Excel, but I would prefer to do this in Alteryx.

 

I've tried using the date/time parse tool and the only thing I've gotten out of that is frustration. If anyone could give me advice on how I should bring in my date field and in what order I should perform the conversions, it would be much appreciated!!

 

 

Highlighted
12 - Quasar

After the Excel file is imported, what is the field type in Alteryx?  string?  date?

 

Before the field is written to your output Excel file, is the field type String?

 

Can you post an example of your input Excel file and workflow?

Highlighted
Alteryx
Alteryx

Hi @frittsb ,

 

Will the attached solution help? I used the DateTime tool by specifying the format of incoming string field as "MM/DD/YYYY" --> the formatted date is automatically transformed into a Date format, and when written to Excel it's also automatically formatted as "MM/DD/YYYY" in date format. See screenshot as attached.

Highlighted
Alteryx
Alteryx

hi @frittsb ,

 

you could try this. it will give you the correct format you are looking for.

bpatel_0-1575479393153.png

 

i hope this helps!

Highlighted
6 - Meteoroid

I appreciate all the quick replies. I understand the recommendations to use the Date/Time parse tool, but something still isn't working for me. I'm limited with what I can share, so going to provide as many details here as I can:

 

Input file = .csv, the dates are formatted as dates within the .csv file

 

Within the workflow, one of the first tools is a select. I select all of those date fields, when I select them, what format should I be using?

Currently if I select date and run the workflow, all of my date fields are blank in the output. I see field conversion errors: "maturity_date: "4/27/2025" is not a valid date"

 

I just tried adding adding date/time parse tools inbetween my input and select tools, each one used 'String to Date/Time format' and the 'MM/dd/yyyy' format. I'm still getting the same error in the output though.

 

I'm not sure I understand why, if my field is formatted as a date in my input data, and I use a select and declare the field a date there, why would I get any errors? I'm not transforming or doing any calculations with the date.

Highlighted
Alteryx
Alteryx

the standard alteryx date is in the format of yyyy-MM-dd which is why you are getting the error when trying to change the format using the select tool.

Highlighted
6 - Meteoroid

That makes sense. I think I realize the source of the error now as well, my date isn't actually in MM/DD/YYYY format, it's not that standardized. The errors I'm getting are related to dates that truncate the leading zero (any month less than 10 and any day less than 10, 7/1/2019 for example).

 

It sounds like I can select these fields as strings and just before writing my output, I would use the date/time converter to convert to a date. But is there a format that will handle dates like I described in the first paragraph?

Highlighted
Alteryx
Alteryx

Hi @frittsb 

 

This solution should help:

mutama_0-1575483333424.png

 

As you can see, even with months < 10 (e.g. 9/2/2019), it will still be converted to 2019-09-02 i.e. 2nd September.

Highlighted
6 - Meteoroid

I'm attaching more screenshots because it's still not working. I've tried this just about every way I know how and I'm still baffled.

 

The end of my workflow uses a select to order and rename columns immediately before writing the output, here is a screenshot.

 

I'm still getting all sorts of conversion errors. I'm bringing the fields in as strings, converting them where highlighted in the screenshot and then selecting as a date in the final select. What am I doing wrong?

Highlighted
12 - Quasar

The Date Time tool should change the field format to Date.  In the final Select tool, you shouldn't need to change the field type.

Labels