Date time format conversion of a datetime field

Question

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

mutama · Answer

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-p/151965

Hope this helps.

Best,

Michael

Kumar_A · Answer

@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.

grazitti_sapna · Answer

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.