String to date

damen · 2023-02-22T17:25:00+00:00

There was an error rendering this rich post.

Hi,

I'm trying to take the 'DUE DATE' column below (covered by the mouse) and turn those numbers into dates. 20123 is supposed to be 02/01/23

The solution I first thought of was to just split the columns but because it is showing up as text, it wont do that.

Is there a beast mode to just pull the last two, the middle two and the remaining digits into their own separate columns?

I am working in magic etl and am hoping to continue to do so

Find more posts tagged with

Magic ETL

Accepted answers

damen

@MichelleH Beautiful. That worked out!

All comments

MichelleH

@damen I'd suggest using the STR_TO_DATE function in the formula tile. Since there may be 5 or 6 digits, you can use the LPAD function to add a leading zero if the length is less than 6.

STR_TO_DATE(LPAD(`DUE DATE`,6,'0'),'%m%d%y')

Here is the KB article with all the date format specifiers to use with the STR_TO_DATE function: https://domo-support.domo.com/s/article/360043429953?language=en_US

damen

@MichelleH Beautiful. That worked out!