Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert String to Date

superjesse
8 - Asteroid

Hello all,

 

How do I convert 09222018 to a date? I can't get the string to date function to work because I think I have to have it in yyyy-mm-dd format. I tried different ways and cannot achieve this. I also tried the datetimeparse formula, but keep getting a null value.

 

DateTimeParse([Timestamp UI],'%mm-%dd-%yyyy')

 

Thanks in advance.

 

Jesse

14 REPLIES 14
DavidxL
9 - Comet

The format you provided was incorrect: see Specifiers https://help.alteryx.com/current/Reference/Functions.htm?Highlight=date

 

I attached a workflow as an example.

superjesse
8 - Asteroid

This community is awesome!!!!!! Thank you so much!!

AKnutson11
7 - Meteor

You may also want to try Datetime tool under the Parse tools. It has similar functionality and is a bit more user friendly if all of your dates are in a clean format like your example. 

 

https://help.alteryx.com/current/DateTime.htm

 

xiaoyan
6 - Meteoroid

Hi all,

Can anyone guide me how to convert the format from 2019-02-26 to a date format like this: ddmmmyy (i.e.,26Feb19)?

I tried to use DateTimeParse "DateTimeParse([Incurred From Date],"%d,%b,%y")", but there's a warning message saying cannot convert "2019-04-22" to a date /time with format "],"%d,%b,%y": Expected separator , ],"%b,%y, got '19-04-22'.

 

I even tried to use the select tool to change the format to date first, but the result keep showing null.

VigneshKS
6 - Meteoroid

1. First use Date time tool from Parse and convert the Date field to a proper Date format

2. Drag formula tool and write the query as DateTimeFormat([Datefield],"%d-%b-%y")

3. Now again drag the formula tool and write the query as (Regex_Replace([Datefield],"[^a-zA-Z0-9]",''))

 

 

This method will give as what you requested. Hope it works for you

VigneshKS
6 - Meteoroid

h

Mateusz_Palasz
5 - Atom

Hi guys

 

I am trying to convert string

04.01.2017 13:48:04

to datetime format by using the following formula

DateTimeParse([column_name],"%d%m%y%h%m%s")

but I got the error

ConvError: Formula (19): DATETIMEPARSE: Cannot convert "04.01.2017 13:48:04" to a date/time with format "%d%m%y%h%m%s": Expected a number for Month: '.01.2017 13:48:04'

could you please help me to solve this?

 

Thank you, Mateusz

Jean-Balteryx
16 - Nebula
16 - Nebula

Hi @Mateusz_Palasz ,

 

Try using %M for month instead of %m. It should work.

 

Cheers,

 

Jean-Baptiste

jessle
8 - Asteroid

Hi Mateusz_Palasz,

You just need to make sure the format you're providing is exact (i.e. it also contains the '.' and ':' delimiters, and a space between the date and time parts) and that you're using the correct specifiers (the hour-minute-second letters need to be capitalised). You can check your specifiers here: https://help.alteryx.com/current/designer/datetime-functions

DateTimeParse([OriginalDate],'%d.%m.%y %H:%M:%S')

20200819 1a.png

(If you want the time included in the output as well, just change the data type to 'DateTime').

You could also use the DateTime tool if you want a friendlier interface:

20200819 1b.png

Hope this helps!

Labels