How to change date to string with In-DB formula tool

[Linders]

Hello! I'm stuck with my In-DB formula to change this date format from 2023-06-29 to 20230629 and into a string. I noticed the date formats are different with the another SAP report tool add on we use (imports as string 20230629) and the Alteryx In-DB SAP HANA ODBC pull (imports as date 2023-06-29). I need these dates to align so the interface text box and action tool works correctly when the user inputs the date, which updates the string downstream. 

For example, the user would input the start and end date in the analytic app (in screenshot). Ideally, this input would then update the string values in 2 different areas of the workflow. The user date input would then be processed by 2 action tools to change the string values for the start date and another 2 action tools to change the string values for the end date. Any recommendations on using the In-DN formula tool to convert this date to a string without the dashes?

Thank you in advance for your help!

[caltang]

Can you cast the date as INT?  -> https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Change-the-Date-format-in-DB-t...

Otherwise you can use the Data Stream In and Data Stream Out to use the normal non-DB tools of Alteryx to convert date into string, then back into DB.

[Linders]

Hi Caltang, thanks for providing your input. I have tried the first solution and many different variations of it but to no avail. I was hoping there was an easier workaround for the In-DB tools. Looks like the second option would be a better approach.

[caltang]

Actually, @Linders - I found something: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/How-to-convert-string-date-YYY...

I think this may be your solution In-DB!

[caltang]

Related posts here:

• https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/In-DB-formula/td-p/619996/page...
• https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/IN-DB-Need-to-Convert-a-Date-f... 

[jdminton]

I've included an example. My dates in-DB were already string without the "-" so I added the dashes first and set a new column as date. Then I converted them back to string and removed the dashes in two separate steps so you could see each separately. You might have been having issues with the quotes if you tried this. With Alteryx, the SQL has double quotes around fields and single quotes elsewhere.

[apathetichell]

Try

TO_VARCHAR("YOURFIELD",'YYYYMMDD')

using formula in-db to create  a new string field. This is is what I see in the SAP S4/HANA documentation.

[apathetichell]

Hi -did you get this to work?