Convert String to date format

[jdevar243]

Hi

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.

[neilgallen · Accepted solution]

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.

[DiegoParker · Accepted solution]

@jdevar243 oh! that's strange

It is working for me:

I wonder what it is.

Best,

Diego

[jdevar243]

@DiegoParker - I figured the mistake i did . I am using yyyymm instead of yyyyMM . Thanks for your help

[Bharadwaj]

Had a similar issue and this (DiegoParker's solution) helped. Thanks