Alteryx Designer Discussions

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

DatetimeParse

Scheruku
8 - 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.

8 REPLIES 8
MarqueeCrew
20 - Arcturus
20 - Arcturus
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 restart. Order shall return.
Please Subscribe to my youTube channel.
Scheruku
8 - 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'

mborriero
11 - Bolide

Why do not you use a DateTime tool?

Capture.JPG

 

 

 

 

Scheruku
8 - Asteroid

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

KaneG
Alteryx Alumni (Retired)

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')

Manjari
8 - Asteroid

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

 

KaneG
Alteryx Alumni (Retired)

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")

rajarkumar
5 - Atom

Input Date is defined as in_date column in text input tool 26/07/2018 18:00:00

 

Formula column Datetime_Final is having following expression.

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

 

Note:   the in_date is string datatype, it is transformed into date using datetimeparse function and then it is changed to string using the datetimeformat date function to the targeted format.

 

in_date                                  DateTime_Final

26/07/2018 18:00:00             26-07-2018 18-00-00

Labels