Using type function within filter of collection filter action

[bbastien]

Hello all,

I am currently trying to make a filter for a collection which has a "Date" collumn, and my aim is to get all items for which that date is between the first and 15th of any month and any year. To that end, I assumed that I could use the < and > operands, but I cannot figure out how to get the value specific to the day, and I would like to avoid using a hardcoded value such as "Date LIKE '01/*' OR Date LIKE '02/*'" all the way to 15.

Is there  some expression to get the date's day value and turn it into a number which can be compared directly within the action's filter field ?

Thank you in advance for your returns !

Bastien

[MichealCharron · Accepted solution]

@bbastien 

Because you, from your examples, have zero padding in your date format it is fairly easy to apply a filter to pull out the rows you want.

You just have to create a filter string that:

1. Changes the date to a string.
2. Extracts the first two numbers from that string.
3. Checks to see if the extracted numbers are less than 16.

"Substring(Convert([Date Column], System.String), 1, 2)<16"

[bbastien]

Thank you for the response ! I just have a follow-up question, but is there some way to "identify" which part of the string is the day, month and such ? As in, if I had a date that is, for example "02/03/11", is there some way to know without asking the system owner which field is which ? Thanks in advance !

[MichealCharron]

@bbastien 

I am not aware of a standard Blue Prism action that can return the date format of the machine that the process is running on. A code stage could be written to return the date format of the machine but it could also be determined by a expression in a decision or choice stage.

If you could use the following in a decision stage:

(""& MakeDate(31,12,1)) = FormatDate(MakeDate(31,12,1), "MM/dd/yyyy")

or have a series of choices depending of the complexity you would be dealing with:

(""& MakeDate(31,12,1)) = FormatDate(MakeDate(31,12,1), "MM/dd/yyyy")
(""& MakeDate(31,12,1)) = FormatDate(MakeDate(31,12,1), "dd/MM/yyyy")
(""& MakeDate(31,12,1)) = FormatDate(MakeDate(31,12,1), "yyyy/MM/dd")
(""& MakeDate(31,12,1)) = FormatDate(MakeDate(31,12,1), "yyyy/dd/MM")

 All those expression cast a Date value to a text and just use the FormatDate function to determine which one it matches.

[bbastien]

It's all right then, I thought it could've been a basic to have dates and datetimes use some properties such as day|month|hour to help identify the format when working with international teams which might use different formats, but I guess that would be a possible waste of space for it.

Either way, thank you for your help and for the expressions !

Bastien