Date time format conversion of a datetime field
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Team ,
Could someone please help me in converting the format of a datetime field .
Input date format : 2020-11-23 Date type : Datetime
Expected output : 23-NOV-20 Date type : Datetime
I have tried the formula DATETIMEFORMAT([orderdate], "%d-%b-%y") but the output comes as string type but I'm expecting as Datetime.
TODATE(DATETIMEFORMAT([orderdate], "%d-%b-%y") ) doesn't help too.
please guide
Thanks in advance.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Kumar_A ,
Alteryx uses the ISO format YYYY-MM-DD HH:MM:SS to represent dates and times. If a DateTime value is not in this format, Alteryx reads it as a string.
If you need to perform further customized date manipulation within Alteryx itself (e.g. count the number of days since last order date, for example), then remember to use the YYYY-MM-DD format prior to such calculation/manipulation, before finally changing the date format to your desired format (e.g. DD-Mon-YY) towards the end of the workflow (e.g. before outputting it to Excel, for instance).
However, this should not be an issue if you need to write back to an Excel and preserve the format as "Date" in Excel. All you need to do is, in the Output Data Tool --> select the "Preserve Formatting on Overwrite (Range Required)" checkbox & specify the Excel Cell Range --> you are able to preserve the Date in Excel in the format you desire (i.e. DD-Mon-YY, e.g. 23-Nov-20).
Please see screenshots & sample Excel file as reference. Hope this clarifies!
Best,
Michael UTAMA
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Kumar_A,
Alteryx uses YYYY-MM-DD HH:MM:SS as the default datetime format. Although you can play around the date field and convert it to some other formats, but those formats are considered to be string data types.
I hope it helps.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks much both for your response.
Dear Utama, My final expectation is to write the field as datetime(datatype) in the format "DD-MON-YY" to my Oracle DB. Is there any workaround within alteryx to achieve this?
Thank you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Kumar_A ,
Yes - have you tried customizing the Pre Create SQL Statement options in the Output Data Tool? So, after you have formatted the column in your desired format (e.g. DD-Mon-YY), before writing back to the Table in Oracle, you can write a simple SQL Statement inside the configuration tab of the Output Data Tool, which will be executed before the table is updated/overwritten.
In the screenshot example below, after I've formatted the [Date_Formatted] column to DD-Mon-YY format, the SQL statement will update the table by creating a new column in Oracle, "Date_Formatted" and setting its Data Type as "DATE". This should change the data type from String in Alteryx to DATE inside Oracle.
For more info on writing Pre/Post SQL Statements and how they work, please take a look at this Community article here: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-Pre-Post-SQL-Statements/ta-...
Hope this helps.
Best,
Michael
