DatetimeParse

[Scheruku]

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.

[MarqueeCrew]

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?

[Scheruku]

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]

Why do not you use a DateTime tool?

[Scheruku]

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

[KaneG]

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]

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]

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]

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