Converting a Vstring to Date

[Wags5]

Hello, any help would be great! I have 3 columns full of dates but the issue is they come into my Alteryx as V-Strings.

Here's an example: Take 250402, this is actually YY/MM/DD.

How can I get a value such as that to be the date format in Alteryx?

Either MM/DD/YYYY or YYYY/MM/DD will work, I am just having the hardest time figuring out if this is possible using a simple formula.

[binuacs]

@Wags5 if you want to change multiple fields to date data type use the multi-field formula tool

[ChrisTX · Accepted solution]

See this page https://help.alteryx.com/current/en/designer/tools/parse/datetime-tool.html

Under Custom Format > Day, Month and Year formats

Limitation with 6-Digit Dates

Because up to 4 digits are read for the year, formats that are intended to have only 2 digits without a separator, such as a 6-digit date stamp (for example, %y%m%d for data resembling 170522 for May 22, 2017), are still read as 4 digits. To work around this limitation, you can...

• Depending on your range of dates, use four digits for the year (for example, 2017 instead of 17).

• Use the RegEx tool to insert a space after the first 2 digits in the string.

Since RegEx can be difficult for some, you could instead use a Formula tool with a formula like this to add a separator to the input value:

Left([Input Date], 2) + "-" +
Substring([Input Date],2,2) + "-" +
Right([Input Date], 2)

Then use the DateTime tool with a Custom Format of 

yy-MM-dd

See attached workflow.

Chris

[Wags5]

Hi Binuacs, this doesn't seem to work for my project, and my mistake but I actually need this to be formatted to work as a "Date" Data Type because I have a filter tool  later on in my project that filters the dates in my "SALE_DATE" column. The "/" breaks don't even appear all that gets outputted is 20250402. 

My filter formulas that I use later on doesn't work with that output it would need to work with this formula: 

DATETIMEYEAR([SALE_DATE]) = DATETIMEYEAR(DATETIMENOW()) AND
DATETIMEMONTH([SALE_DATE]) = DATETIMEMONTH(DATETIMEADD(DATETIMENOW(), -1, 'months'))

Please let me know if there is any other way to get the format changed.

[binuacs · Accepted solution]

@Wags5 YYYY-MM-DD is the date format in Alteryx, any other format will be in the string data type. If you want your date fields to be in Alteryx DATE datatype, then you need to convert them into YYYY-MM-DD format