community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

Learn More
SOLVED

Date Time Conversion

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.

Quasar
Quasar

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?

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!

Quasar
Quasar

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.

Highlighted
Asteroid

Sure, thanks!

Quasar
Quasar

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!

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