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 8 digit number to a date

RitaB
7 - Meteor

The database I'm extracting from uses an 8 digit number in FixedDecimal format for the date - YYYYMMDD.  If I were in Excel, I'd use the date function.  Assuming the date is in cell A1, my formula would be =date(left(A1,4),mid(A1,5,2),right(A1,2)).  So 20191231 would convert to 12/31/2019.  How do I do this in Alteryx?  It seems I can only convert from string to date, not number to date.  Thank you.

4 REPLIES 4
LordNeilLord
15 - Aurora

Hey @RitaB 

 

Bookmark this page...you'll need it A LOT! https://help.alteryx.com/10.1/Reference/DateTimeFunctions.htm

 

You need to Parse the date into an "Alteryx" date......you can use the DateTime Tool or the formula tool.

 

The formula would be DateTimeParse([DateField], "%Y%m%d")

CharlieS
17 - Castor
17 - Castor

Here's the formula I would use so the string conversion is included in the date parse

 

DateTimeParse(tostring([Input]),"%Y%m%d")

 

Where [Input] is your 8-digit numeric field. This formula will convert to the ISO 8601 format, which is the Alteryx standard for date values. If the input in "20190712", then the output would be "2019-07-12"

 

If you want this result in a different format, you can take this value and convert it to the MM/DD/YYYY with this modification:

 

DateTimeFormat(DateTimeParse(tostring([Input]),"%Y%m%d"),"%m/%d/%Y")

 

Keep in mind that the result of this formula must be a string and not a date field. Alteryx only recognizes the ISO 8601 format as date values. 

RitaB
7 - Meteor

Thank you - this worked perfectly.  

RitaB
7 - Meteor

Thank you! - I will definitely bookmark that page.

Labels