Convert String to date format

i have string format (201901) from source and want to convert into date field . I tried to use both datetime tool and formula tool but of no use.

i want to convert (201901) string to 2019-01-01 DATE format. Please let me know if anyone come across the solution for this.

Developer Tools

Accepted answers

neilgallen

Hi @jdevar243 you can use a datetimeparse formula within a formula tool to create this.

It would be formatted as

datetimeparse([string],"%Y%m")

This new field would be setup as a date format, and then you can drop the old field if not necessary.

DiegoParker

@jdevar243 oh! that's strange

It is working for me:

I wonder what it is.

Best,

Diego

All comments

neilgallen

Hi @jdevar243 you can use a datetimeparse formula within a formula tool to create this.

It would be formatted as

datetimeparse([string],"%Y%m")

This new field would be setup as a date format, and then you can drop the old field if not necessary.

DiegoParker

Hi Jdevar,

You can use the DateTime tool configured like this:

Custom: yyyyMM

Hope this helps.

Best,

Diego

jdevar243

@neilgallen - Thank you

jdevar243

@DiegoParker - I tried this approach but it is populating 2019-01-01 for all the rows .

PCAM

This is very common in our company. I often need to do several at once. The basic formula I use is Left([string], 4) + Right([string], 2) + '01' and set the output format to Date.

If you are using multiple fields for the same type of formatting, multi-field is your friend. Select the fields you want to format, and use the same formula with this change: Left([-current field-], 4) + Right([-current field-], 2) + '01'. Change your output type to date. You can create new fields or simply update the originals.

You can also use the substring formula.I often use it for dates and time stamps that have odd formatting. We have lots of that in our system.

DB2, 30+ years old and still going strong. But there have been many many fingers in the pot over the years. We have 6 different types of dates formatted, and frequently need to parse them out either for dynamic input or for clarification on output.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703