10-08-2019 02:22 PM - edited 07-15-2021 02:54 PM
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
Procedure
'"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.
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.
can you attach a screeshot of the workflow for how this is done pelase?
@kbenson Thanks! That worked perfectly.