topic RE: Convert String to Date in Product Forum

Convert String to Date

Dhenn_MarkEspir — Fri, 24 Jun 2022 02:18:00 GMT

Hi,

Is there an easier way to convert a string to date format? For example the date string has 1st, 2nd, 3rd, 4th, 5th, 6th... 31st. of June

Perhaps converting 18th March 2021 to 18/03/2021?

------------------------------
Dhenn Mark Espiritu
RPA Consultant
EY
Asia/Manila
------------------------------

RE: Convert String to Date

devneetmohanty07 — Fri, 24 Jun 2022 07:37:00 GMT

Hi Dhenn,

For your requirement you can create a new business object called as 'Date Time Manipulation (Advanced)'. Provide the below Name Space Imports and External References in the Page Description stage of your Initialise action keeping your language set up as 'Visual Basic' as shown below:

Now you can create a new action called as 'Parse Date'. This action will have two input arguments:

1) Input Date (Text): This is the required date string from where you want to generate a valid date data item.
2) Date Formats (Collection): This collection will have one column called as 'Date Format' which will comprise of all the possible date formats in each row which your input date string can have:

Also, create two output arguments:

1) Output Date (Date): This is the output date that will be generated from this given action.
2) Message (Text) : This will have the error message in case of any exception.

Now add a code stage named 'Parse Date' as shown in the workflow and map the arguments as below and provide the same code as well:

Dim temp_message As String
For Each format_item As DataRow In Date_Formats.Rows
	
	Try
	
		Output_Date = DateTime.ParseExact(Input_Date, format_item(0).ToString, System.Globalization.CultureInfo.InvariantCulture).Date
		Message = "Success"
		Exit For
	
	Catch Ex As Exception
		
		temp_message = format_item(0).ToString & " - " & ex.Message.ToString
		
		If Message.Equals("") Then

			Message = temp_message

		Else
	
			Message = Message & Environment.NewLine & temp_message
		
		End If

		Continue For

	End Try
	
Next

That's all and you are set now to publish this action and make the call from Process Studio.

Test Scenario:

Just make sure you pass the input date in the following format to this action: 12 March 2022

So, if you input text has any date as 12th March 2022, ensure you remove the 'th', 'st' or the 'nd' portion first from your input string.

Before this action you can simply call a calculation stage and do this operation with the following expression:

Replace([Input Date],Mid([Input Date],1,InStr([Input Date], " ")-1),Replace(Replace(Replace(Mid([Input Date],1,InStr([Input Date], " ")-1),"th",""),"nd",""),"st",""))

Once you have the input in a format which can either be: dd MMMM yyyyy or d MMMM yyyy

dd MMMM yyyy will be used for any date which can have two digits in the day place like 12 August 2022, 21 March 2022 etc.
d MMMM yyyy will be used for any date which can have one digit in the day place like 1 January 2022, 9 December 2022 etc.

Simply create a collection called as Date Formats with both these values:

So now in the action, you simply pass the input date that we got from the calculation stage along with the date formats collection and you would get your output as shown below:

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: Convert String to Date

MichealCharron — Fri, 24 Jun 2022 13:22:00 GMT

@Dhenn Mark Espiritu

You can use the "Regex Replace" action in Blue Prism's "Utility - Strings" VBO to rearrange the important parts of the string and then cast it directly into a "Date" data item.

"(\d+)\w{2}\s(\w+)\s(\d{4})"
The regular expression pattern captures:

The digits of the day (ignoring the suffix).
The full month name.
The four digits of the year.

"$2 $1, $3"
The replacement string uses the capture positions to produce (March 18, 2022😞

Capture 2 - The full month name.
Capture 1 - The day (with a comma after it).
Capture 3 - The four digits of the year.

The output of the action, although set as text, can cast directly into a "Date" data item.

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

RE: Convert String to Date

Dhenn_MarkEspir — Mon, 27 Jun 2022 00:55:00 GMT

Hi @MichealCharron, this makes more sense. Will try it on.
------------------------------
Dhenn Mark Espiritu
RPA Consultant
EY
Asia/Manila
------------------------------

RE: Convert String to Date

MichealCharron — Mon, 27 Jun 2022 12:32:00 GMT

@Dhenn Mark Espiritu

You can find the "Regex Replace" action in the Blue Prism "Utility - Strings" VBO in the Blue Prism Digital Exchange.

Blue Prism Digital Exchange - Utility - Strings

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------