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 format

jdevar243
7 - Meteor

Hi

i have string format (201901) from source and want to convert into date field . I tried to use both  datetime tool and formula tool but of no use.

 

i want to convert (201901) string to 2019-01-01 DATE format. Please let me know if anyone come across the solution for this.

8 REPLIES 8
neilgallen
12 - Quasar

Hi @jdevar243 you can use a datetimeparse formula within a formula tool to create this.

 

It would be formatted as 

datetimeparse([string],"%Y%m")

 

This new field would be setup as a date format, and then you can drop the old field if not necessary. 

DiegoParker
10 - Fireball

Hi Jdevar,

 

You can use the DateTime tool configured like this:

 

DiegoParker_0-1582560091722.png

 

Custom: yyyyMM

 

Hope this helps.

 

Best,

Diego

 

jdevar243
7 - Meteor

@neilgallen - Thank you

jdevar243
7 - Meteor

@DiegoParker - I tried this approach but it is populating 2019-01-01 for all the rows .

PCAM
7 - Meteor

This is very common in our company. I often need to do several at once. The basic formula I use is Left([string], 4) + Right([string], 2) + '01' and set the output format to Date. 

 

If you are using multiple fields for the same type of formatting, multi-field is your friend. Select the fields you want to format, and use the same formula with this change: Left([-current field-], 4) + Right([-current field-], 2) + '01'. Change your output type to date. You can create new fields or simply update the originals.

 

You can also use the substring formula.I often use it for dates and time stamps that have odd formatting. We have lots of that in our system.

 

DB2, 30+ years old and still going strong. But there have been many many fingers in the pot over the years. We have 6 different types of dates formatted, and frequently need to parse them out either for dynamic input or for clarification on output.

DiegoParker
10 - Fireball

@jdevar243 oh! that's strange

 

It is working for me:

DiegoParker_0-1582560985490.png

 

I wonder what it is.

 

Best,

Diego

 

jdevar243
7 - Meteor

@DiegoParker - I figured the mistake i did . I am using yyyymm instead of yyyyMM . Thanks for your help

Bharadwaj
5 - Atom

Had a similar issue and this (DiegoParker's solution) helped. Thanks

Labels