Sukesh Kumaru
Active Member
Hi,
Currently Date field in collection is of which type "Text" or "Date",
If it is text convert it to date format and try sorting that particular date column and check the results.
Currently Date field in collection is of which type "Text" or "Date",
If it is text convert it to date format and try sorting that particular date column and check the results.
Sukesh Kumaru
Active Member
If it is in text format sorting doesn't gives best results........
How can I convert the data type of a collection field from Text to Date?
We can convert the Date field values from text to Date type. For this we will have to use a loop so that it access all the rows of collection one by one and then use a calc stage to convert the Date field values from text to date using the convert function. But after this we will also have to store this converted value to the Collection Date field but as you know the collection date field is of text type , it will not accept the converted value which is of DATE type.
SO can you tell how can I convert the data type of a collection field from Text to Date
You can still have the dates stored in the Text column without having to converting the data type to date. But things like sorting etc won't work if that is why you are looking to convert to a Date column.
For that you can look for these options.
i. Take the code of the 'Append field (Text)' and create a similar action for the Date column. Change the data type in the code to a Date.
This will append a new column with Date datatype and then you can set the date value to this new column and delete the Text column.
ii. If you want to do it without a code stage then have a new empty collection (DateCollection) with a Date Column. So now your two collections should look this way
TextCollection (with the date in text format):
Column1 | Column 2 | TextColumn (Text)
1 | ONE | 20/11/2018
2 | TWO | 05/12/2018
DateCollection (empty collection with a single column with Date data type):
DateColumn (Date)
Now use the Merge Collection on these two collections.
After merging the final MergedCollection would look like this
MergedCollection:
Column1 | Column 2 | TextColumn (Text) | DateColumn (Date)
1 | ONE | 20/11/2018 |
2 | TWO | 05/12/2018 |
Now loop through the Merged collection and set the new dates to the DateColumn like you said in your post.
Column1 | Column 2 | TextColumn (Text) | DateColumn (Date)
1 | ONE | 20/11/2018 | 20/11/2018
2 | TWO | 05/12/2018 | 05/12/2018
Now Delete the TextColumn
Column1 | Column 2 | DateColumn (Date)
1 | ONE | 20/11/2018
2 | TWO | 05/12/2018
Be noted that Blue Prism has an issue while sorting a date time column in a collection.
For that Refer Post #2 here and you might want to consider the options given there.
Hi VJR,
Thanks for reply.
But in the code stage, when I am trying to change the data type, it is getting changed to datetime not just date.
Please see in the image also. In the Inputs, I have changed the data type to Date, but in code it says System.Date Time in the Parameters section.
Due to this the time is also getting added after the date.
But we want only date.View attachment Code.jpgView attachment Code 2.jpg
Last edited:
While inserting the date into the collection have you tried Formatting the data item (even though it is displaying as a date) only to a date using the Format functions and then inserting? If you do that what does it show?
Did you mean the Format functions? What value shows up in the datetime column when you pass only the date?
Also check with FormatDate.
Okay, to answer your question about using FormatDate you had to use like the below so that it does not get converted to Text.
ToDate(FormatDate([Coll8.OnlyDate], "dd/MMM/yyyy"))
But I have checked doing so and it is of no use for you because the output it shows is as below.
12/30/2017 12:00:00 AM
It takes a default of 12:00:00 AM because the data type of the column is still DateTime as shown in your Code screenshot.
You could research to see if datatable allows a Date datatype for its column (not the actual data but its column datatype). Collections are the .net datatables in Blue Prism.
So an approach is to have an empty Date column in a separate collection and then merge it to your original collection as explained in post #6 above or have an additional Date column in the original collection itself and then set only the date part to the new column and then delete the text based date column in either of the cases. A customised Object can also be created to do this for any collection with a new action such as Append Column (Date) which does the Merging inside it.
Hope you have also seen the other issue I have mentioned at the bottom of the post while sorting date time columns.