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

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!

Capture.PNG

Date Time

Apps

In Database

Expression

In-Database

Accepted answers

All comments

caltang

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

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-YYYY-MMM-to-date-yyyy-mm-dd-IN-DB/m-p/1112047/highlight/true#M276111

I think this may be your solution In-DB!

caltang

Related posts here:

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.

String Date In-DB.yxmd

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?

Quick Links

This months top contributors

FláviaB 262

caltang 177

DanM 167

IraWatt 157

gawa 157