DateTime Conversion Error

Hello,

I have two separate date formats in my raw data file:

Column 1: 1/5/2023 8:59 ("m/d/yyyy h:mm")
Column 2: 7/2/2021 1:43:00 PM

How do I convert it to ISO format? I tried both - the DateTime Tool and the DateTimeParse function. I tried entering the custom format of "M/d/yyyy h:mm" in the DateTime tool, but the tool isn't recognizing the hour as a single digit. I did not even come close when I tried entering the date format in the DateTimeParse formula.

Could you please help?

Date.jpg

Developer

Date Time

Accepted answers

flying008

Hi, @apathetichell @Astro55

I'm sorry for my mistake, the part of format must be : %m/%d/%Y , not the %M/%d/%Y

😅

Astro55

@apathetichell -Thank you so much for your help!!! I really appreciate it!

All comments

flying008

Hi, @Astro55

Please see that below:

录制_2023_03_02_09_13_49_335.gif

Astro55

@flying008 - Thank you for responding so quickly! I tried both solutions (since I'm unsure how exactly to define my input format as), and I am still getting conversion errors. I am attaching a sample of my data, in case that helps. Apologies for not attaching it earlier.

Dates.xlsx

apathetichell

When I opened your file in Designer - both fields natively opened as datetime. You aren't seeing that? is this the correct input file?

@flying008 does %M work for month in the datetime tool - in datetimeformat that's minutes - and in the instructions for the datetime tool it reads as though M should be month - and %M should correspond to the standard datetime flag of minutes

flying008

Hi, @apathetichell @Astro55

I'm sorry for my mistake, the part of format must be : %m/%d/%Y , not the %M/%d/%Y

😅

Astro55

@apathetichell - the input file is correct; I get the same result as you when I upload the file in Designer (i.e., it reads it as a DateTime format). If Alteryx automatically classifies both fields as DateTime, does it mean I don't need to manually change the field to read in the ISO format?

My confusion lies here: My understanding was that Alteryx only understands the format YYYY-MM-DD and that I had to change all date formats to match this, regardless of whether Designer automatically classified it as DateTime or not. The Excel file shows the date as (for example) "1/6/2023 8:59" or "7/2/2021 1:43:00 PM", which is not the YYYY-DD-MM format.

What I was therefore trying to do, was convert this Excel format into the ISO format by:

Using the Select Tool to manually change the format from "DateTime" to "V_WString."
Using either the DateTime Tool or the DateTimeParse formula to change the date into an ISO date format

Question 1: Is this not required? I apologize for the fundamental question, I'm still relatively new to Alteryx, and DateTime continues to stump me despite my research.

Question 2: I also just noted that when I converted the field to a string and ran the output, the string was in the ISO format. Is this because Alteryx automatically read it as the standard ISO format, or is this because the input data is in the ISO format - even though it inexplicably shows up as "m/d/yyyy h:mm" when I look at its format type in Excel?

Astro55

@flying008 - Thank you so much for the update! I continue to learn.

I'm still getting an error when I use the custom incoming format of "%m/%d/%Y %l:mm". I wonder if it's related to @apathetichell 's observation above.

apathetichell

Your dates are Alteryx dates. You don't need to do anything else. Date conversions are when Alteryx reads in dates as strings (you can test by adding a string value to the same field in your underlying excel - or by converting in excel to strings). In your case - just process as is and don't convert.

Astro55

@apathetichell -Thank you so much for your help!!! I really appreciate it!

Astro55

@flying008 - Thank you so much for your help - this worked on a .csv file I had!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703