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 Knowledge Base

Definitive answers from Designer Desktop experts.

Change Date Type Coming Out of Date Interface Tool to Match In-DB Field Date

DanC
Moderator
Moderator
Created

How To: Change Date Type Coming Out of Date Interface Tool to Match In-DB Field Date

A field in my database has dates in the following string format: YYYYMMDD. However, the date format coming out of the Date Interface tool is YYYY-MM-DD. How can I change the date format coming out of the Date interface tool to match the date field in my database? I am pulling the data In-DB, so I don't want to change the date format of my data, which would require me to pull the data out of the database, hence, slowing things downs tremendously.

Prerequisites

  • Product - Designer

Procedure

  1. In the Action Tool created between the Date interface tool and the Filter In-DB tool, choose the 'Update Value with Formula' action option.
  2. Select the 'Expression - value' in the 'Value or Attribute to Update' section.
  3. In the Formula section at the bottom of the Action tool configuration window, add the following formula:
    '"DateField"' + "=" + "'" + REGEX_Replace([#1], '-', '') +"'"

This formula will remove the dashes in between the year and month and month and day from the date passed in by the Date interface tool in order to match the format of the field in the database. You can use other formulas, such as DateTimeFormat or DateTimeParse to modify the Date interface format to other formats as well.

Comments
kbenson
5 - Atom

If you are using the Date Interface Tool to filter an Input Data Tool using Oracle SQL, an alternative to the above is to simply set the "Pre SQL Statement" option on your Input Data Tool to: ALTER SESSION SET nls_date_format = 'YYYY-MM-DD'. This will allow you to easily filter your Oracle SQL results based on a user-specified date parameter.

KCAgentProvocateur
8 - Asteroid

can you attach a screeshot of the workflow for how this is done pelase?

TimN
13 - Pulsar

@kbenson Thanks!  That worked perfectly.