Formula In-DB to convert datetime to date

gwiz

I am connecting to an oracle database via alteryx. Working to convert a DateTime format to Date from this oracle db in a formula in-db tool. Currently the field is of datetime with YYYY-MM-DD 00:00:00. Basically I just want it with the date information, no time. So far I've have tried TRUNC(datefield, 'YYYY/MM/DD') and CAST(datefield AS DATE) , however my efforts are not working. How could I accomplish this? Thank you for the help!

AbhilashR

Hi @gwiz - are you able to use TRUNC to convert the field from datetime to date when you run the sql via an IDE (e.g. pl sql or toad)? Referring to this stackoverflow link, the format in which oracle returns the date depends on how your environment is configured.

That being said, would it work if you convert the datetime field to char to remove the time element? TO_CHAR(SYSDATE, 'YYYY/MM/DD') would be a sample command.

Alternatively, you can bring-in data using in-DB tools into Alteryx and use a regular Select tool to switch it from DateTime to Date. 

 

The approaches above are just suggestions in case you haven't already tried them out.