Date Conversions from CSV

[RogerA]

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

[LordNeilLord · Accepted solution]

Hey @RogerA

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

[BenMoss]

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

[RogerA]

Thank you both!

[SorinDiaconescu]

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"

[BenMoss]

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

Ben

[SorinDiaconescu]

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 :).