Convert 8 digit number to a date

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.

Date Time

Accepted answers

CharlieS

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.

All comments

LordNeilLord

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

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

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

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

Thank you - this worked perfectly.

RitaB

Thank you! - I will definitely bookmark that page.

Quick Links

This months top contributors

FláviaB 262

caltang 177

DanM 167

IraWatt 157

gawa 157