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.

Date time format conversion of a datetime field

Kumar_A
6 - Meteoroid

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.

 

@mutama 

5 REPLIES 5
Kumar_A
6 - Meteoroid

@KevinC @mutama  @yjd  Can you please help with your expertise here.

mutama
Alteryx
Alteryx

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

grazitti_sapna
17 - Castor

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.

 

 

Sapna Gupta
Kumar_A
6 - Meteoroid

@mutama @grazitti_sapna 

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.

mutama
Alteryx
Alteryx

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.

mutama_0-1606273709305.png

 

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

 

Labels