String to Date format

[AjaySetty]

How do I convert a string format to Date or vice versa.

I have two sets of data where one has month as 'JAN-21' in string format and other one has month as '01-Jan-21' in date format, problem is in output file where both are merged into a column after changing formats as "%b-%y" but are recognized separately.

Even when I try to make a pivot from the output its showing up differently, tried a lot to convert either of them but no luck.

[atcodedog05]

Hi @AjaySetty 

Here is how you can do it. You can use formula like below to convert to proper date format.

IF REGEX_Match([Field1], "\u+\-\d+") 
THEN DateTimeParse([Field1],"%b-%y") 
ELSE DateTimeParse([Field1],"%d-%b-%y")  ENDIF

Workflow:

Hope this helps : )

[Laurap1228]

Hi,

I find it's best to get fields into the same format with the same metadata before I merge them. See attached example. Hope this helps!

[AjaySetty]

Actually they are coming in from different inputs and I have managed to make them appear similarly but the format remains a challenge coz even though they are similar (Jan-21) in output one is treated as date and other as string.

I used your formula in the end before output on the column I need but received Null, also not able to change data type in formula tool.

And this is how the data is in flow:

[atcodedog05]

Hi @AjaySetty 

You wont be able to change datatype of existing column in formula tool. You need to create a new column (type date) which can be used for mapping.

Can you show me the null issue you are facing?

[AjaySetty]

Hey @Laurap1228 

Not working, anything wrong here?

[atcodedog05]

Hi @AjaySetty 

Got it what you are speaking about. Can you please run the workflow once. The datastream is not updated hence its showing null.

[AjaySetty]

So this is what is happening, how to solve this data conversion errors?

[atcodedog05]

Hi @AjaySetty 

You can give datetime parse tools a try.

Workflow:

Hope this helps : )

[HomesickSurfer]

Hi @AjaySetty 

Are you ok with both input streams output as 'JAN-21', given that one doesn't have the day and may not want to assume the 1st day of each month?

This should work, without conversion errors.

Extract to C:\Temp to test.