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

Date Time Conversion

timewaste
8 - Asteroid

Hello Peers,

 

Can anyone help me convert this Oracle SQL statement into a formula tool expression? Appreciate your time.

SELECT to_char(replace(replace(to_char(SYSDATE ,'MMDDYY HH24:MI:SS'),':',''),' ','')) vdate FROM DUAL;

 

My formula though is this:

 

ReplaceChar(ReplaceChar(ReplaceChar(ToString(DateTimeNow(), 0), '-', ''),' ', ''),':','')

Thanks.

6 REPLIES 6
Treyson
13 - Pulsar
13 - Pulsar

hello @timewaste!

 

I am curious about this. 

 

Are you just converting a date that looks like "2018-01-01 00:00:00" to a string value like 20180101000000?

Treyson Marks
Senior Analytics Engineer
timewaste
8 - Asteroid

So in Oracle, the date unlike Alteryx looks like this... 13-AUG-2018... which using my above conversion.. can become something like this..

081318124741 (MMDDYYHHMISS)

 

whereas in Alteryx the data is accepted only in this format.. 2018-08-08 which is not helping me as I dont want all YYYY as part of my string in the output. I do know we can tweak a bit using DateTimeFormat(dt,f) but I am unable to get there...almost there!

Treyson
13 - Pulsar
13 - Pulsar

Okay, I am still having a bit of a hard time following exactly what the ask is, however, I would suggest that you check out the custom parsing option on datetime parse.

 

You can either send a datetime in as the format you are asking for (dd-Mon-yy) or take that format and convert it to a datetime.

 

I have attached an example workbook for you.

Treyson Marks
Senior Analytics Engineer
timewaste
8 - Asteroid

Sure, thanks!

Treyson
13 - Pulsar
13 - Pulsar

No problem!

 

Whenever you figure it out it would be super helpful to post what you did back up here for all the other Oracle users!

Treyson Marks
Senior Analytics Engineer
timewaste
8 - Asteroid

@Treyson Absolutely! Here's another solution I was helped with using formula tool.

 

DateTimeFormat(DateTimeNow(),"%m%d%y%I%M%S") --- This gives my desired output (MMDDYYHHMMSS)

 

Eg for today at 1:43PM.. the output would be 081318014332.

 

Thanks!

Labels