Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Check out our powerful new search update! You can read more here. Please let us know if you have any feedback by creating a topic here.
SOLVED

Date Conversions from CSV

Asteroid

I'm reading the documentation on date parsing, and getting confused, there's a lot you can do with it.  The date in my CSV looks like:

 

Aug 18 2017 12:00AM

Sep  1 2017 4:26PM

Jan 17 2018 8:44PM

Feb  9 2018 10:20AM

 

I am trying to convert it using "String to date/time format" with the Date/Time parse tool, using custom format, but no matter what i am trying in the field where i specify my format it wont won't.  Any ideas?

 

thanks

Highlighted
Alteryx Certified Partner

Hey @RogerA

 

Try this in the formula tool: DateTimeParse([Field1],"%b %d %Y %I:%M%P")

 

DateParse.PNG

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Just to add to Neils solution, if you want to use the datetime tool you would simply use the 

 

%b %d %Y %I:%M%P

 

part as your custom statement.

 

Ben

Highlighted
Asteroid

Thank you both!

Highlighted
Meteoroid

Hi guys,

 

Tried the formula and the DateTime tool , but i get errors: "ConvError: Select (15): Start Date: "01/01/2018" is not a valid Date"

 

What do you suggest?

thanks, Sorin

 

Edit: I formated the csv columns as date, closed and saved the file. Now I only get errors when i have "ConvError: DateTime (14): End_date: Cannot convert "31/12/9999" to a date/time with format "%Y-%m-%d": Day number is out of range 1..31: '9999' Record #33"

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@SorinDiaconescu can you post examples of your incoming data and what function you have tried to apply.

 

Ben

Highlighted
Meteoroid

here you go. Now i only get the error for 31/12/9999. I have modified the inital csv to cut for you, so maybe it does something to the format. I the fix is to change the type in excel happy to do it :).

Labels