Date conversion changes all days to "01"

[jmason2]

Hey there,

I am using DateTimeFormat and DateTimeParse to convert v_strings that are in this format dd/mm/yyyy to a date format yyyy-mm-dd

However all days are converted to 01. For example 15/11/2008 is converted to 2008-11-01 when it should be 2008-11-15 this occurs for all records.

This is the formula I am using: 

DateTimeFormat(DateTimeParse([Date],"%Y/%m/%y"),"%Y-%m-%d")

Please help me fix this and thank you in advance!

[lmorrell · Accepted solution]

Hi @jmason2 

The below formula will transform your example date (15/11/2008) to a date format by specifying that the current string is in the format "date"/"month"/"Year".

datetimeparse([Date], '%d/%m/%Y')

The inbuilt Alteryx date format is Year-Month-Day, removing the need for the DateTimeFormat part of your formula. 

Datetimes can get pretty tricky - so a super helpful page to have open when parsing dates is the Alteryx DateTimeFunctions reference page.

The below workflow is attached, with your example date as a reference. 

Hope this helps!

[jmason2]

This works! Thank you so much 🙂