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.

[gwiz]

@AbhilashR thanks

The reason i would like to do this with the db tools is because it can be tons of records to process in a regular select or formula tool so I don't want to have that much processing in alteryx. In the formula in-db tool, do I have to use SELECT _____ FROM table or can I just use TRUNC()?

[lasmithfla]

Did you ever get a final resolution for this?  I need exact same thing

[apathetichell]

@lasmithfla are you using oracle - are you using TRUNC in Formula-In DB? Can you try:

TRUNC(datefield, 'YYYY-MM/-DD') - where datefield is your datefield In-DB (ie "datefield" ) and placing that in a new Date type column?