datetimeparse - need to convet 01/01/2020 (mm/dd/yyyy) from string to date fromat

[Naga]

Hi Team,

Need help.

datetimeparse - need to convet 01/01/2020 (mm/dd/yyyy) from string to date format as 01/10/2020. Only change I need is string to date format, which I am unable to get, when I transfer to destination.

Thanks
Naga

[MarqueeCrew]

DateTimeParse([Field],"%m/%d/%Y")

 cheers,

 mark

[danilang · Accepted solution]

Hi @Naga 

Within Alteryx, all Date type fields have the following format YYYY-MM-DD.  Any other format, like MM/DD/YYYY, must be stored as a String.  When you write to various destinations,  Alteryx takes care of converting the Date field to the correct format for the destination. From there, it's the destination that determines how the date will appear.  For instance In the attached workflow, I have one Date field with 2020-01-13 and another DateAsString(String) with "01/13/2020".  When these are output to Excel, the Date field gets written with a Date format determined by my regional settings.  The string field gets written as a string. 

If I output to a SQL database, the Date field will be stored internally as a native SQL Date and the string will be stored in some variant of a Char field.  When the Date is retrieved in a Select statement, the value will be formatted according the default output format specified in the database. 

The exception to this rule is for the various text-based formats, csv, xml, json.  These formats don't have a specific format for data fields, so Alteryx writes the value in the YYYY-MM-DD format.  If you need a different format, uses the DateTimeFormat() function to convert to a string field in the format that you require.

Dan