How can I convert the data type of a collection field from Text to Date?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.
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.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
Hi VJR,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.
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?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 2748View attachment 2749
Yes, I formatted it using the Convert function but still the value in the new column is showing up as Date TimeWhile 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?Yes, I formatted it using the Convert function but still the value in the new column is showing up as Date Time
View attachment Append Field (date).jpgDid you mean the Format functions? What value shows up in the datetime column when you pass only the date?
Also check with FormatDate.
But doing that will convert the date to text format, if date is displayed as 12/10/2018, then formatting it will show 10 Dec 2018Also 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.But doing that will convert the date to text format, if date is displayed as 12/10/2018, then formatting it will show 10 Dec 2018