cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

DatetimeParse

Asteroid

Hello All,

 

I'm new to the Alteryx. I'm trying to convert the datetime string to datetime format for the data below.

 

2016-11-01 07:17:58 

 

I'm using this function "datetimeparse([DeliveryDate],'%y-%m-%d %X')" and i'm getting the below error -

 

Formula (6) DATETIMEPARSE: Cannot convert "2016-11-01 15:37:20" to a date/time with format "%y-%m-%d %X": Hour is out of range 1..12: '15:37:20'

 

Please let us me know the right way.

Alteryx Certified Partner
Alteryx Certified Partner
I'm a little confused. Your string is ripe to be considered a datetime data type. Why not use a select tool and cast it as such?
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Asteroid

Yes, we can do that but we wanted to do it through the formula.

 

We were able to convert it to date using :datetimeparse([DeliveryDate],'%Y-%m-%d %H:%M:%S'). Most of the values are converted but for some of them it's throwing an error as below 

 

Formula (6) DATETIMEPARSE: Cannot convert "DeliveryDate" to a date/time with format "%Y-%m-%d %H:%M:%S": Expected a number for year: 'DeliveryDate'

Highlighted
Alteryx Partner

Why do not you use a DateTime tool?

Capture.JPG

 

 

 

 

Asteroid

I have around three date fields so i need to use the datetime tool for each field?

Alteryx
Alteryx

The problem in the initial formula is that %X is the time in 12hr notation with AM/PM, you have the time in 24hr notation.

 

In order to parse the time that you have, you should be able to use:

datetimeparse([Field1],'%y-%m-%d %H:%M:%S')

Meteor

Hi @mborriero and @KaneG

 

I tried 2 different methods to parse a date i have "26/07/2018", which is a string 

1. Used the datetime tool, the output is "2026-07-20 18:00:00"

2. Used the formula "DateTimeParse([Date],"%Y-%m-%d %H:%M:%S")", the output is "2026-07-20 18:00:00"

 

I need the output: 26-07-2018 18:00:00 in datetime data type

Not sure where i am going wrong, requesting your help

 

Alteryx
Alteryx

In order to have it in DateTime format, it will need to be 2018-07-26 18:00:00

 

Where is the 6pm time coming from? The formula to get it into DateTime Format will be "DateTimeParse([Date],"%d-%m-%Y %H:%M:%S")

Labels