Converting YYYYMMDD String to a Date YYYY-MM-DD but getting a NULL

I am trying to convert a string field to a specific date format. After reviewing previous posts and trying several examples provided, I am still seeing nulls returned.

The string field I need to convert has a couple of different formats of the data, so I am using a Text to Columns tool to isolate the string of data I need. When adding the formula tool to create a new date field it is returning null.

ToDate(datetimeformat([DOS1],'%Y-%m-%d'))

I have also tried using the DateTime tool to convert string to date with yyyy-MM-dd format but that is also returning null.

See attached workflow to view the current data coming in and the expected output.

Example Input:

20221201

20221202-20221203

Expected Output:

2022-12-01

2022-12-02

Datetime.yxmd

Date Time

Accepted answers

myersm4

Thanks for the fast replies!! I had a light bulb moment after I posted this question and figured it out. I used: DateTimeFormat(DateTimeParse([DOS1],'%Y%m%d'),'%Y-%m-%d')

and no more nulls!

Date Time Parse was the key

All comments

Felipe_Ribeir0

Hi @myersm4

Use this function

datetimeparse([DOS1], '%Y%m%d')

Datetime.yxmd

binu_acs

@myersm4 One way of doing this

Datetime.yxmd

myersm4

Thanks for the fast replies!! I had a light bulb moment after I posted this question and figured it out. I used: DateTimeFormat(DateTimeParse([DOS1],'%Y%m%d'),'%Y-%m-%d')

and no more nulls!

Date Time Parse was the key

BLachney

My data comes in as mmddyyyy (vstring). I've tried using all of the examples for an input string that arrives as yyyymmdd in various ways and nothing works. I still get a null value. Does anyone have a solution for converting from mddyyyy format?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703