Collection filter by Date

hamzakhan

New Member
Hi Gurus,

I currently have a collection ~2000 Rows. there is Col1 (text) = 6/07/2019 17:03.
Now I need to filter out all ROWS from the collection where the dates has PAST (i.e Past dates)
my excel formula was simple "=IF(Col1<NOW(), "Past","Still Time") = only need PAST"

If anyone can help guide me to how to do this using 1. "Data - OLEDB - Get Collection" - what would my SQL query be?
or by using Utility Collection Manipulation - Filter Collection. What would my Filter text be?

Really appreciate your help, i'm not confident in using dates in BP yet.
 

gil.silva

Active Member
Hello hamzakhan,

Using Utility Collection Manipulation - Filter Collection, your filter should be:
"Column name < '" & Now() & "'"

The single quotes before and after the function are very important!

ATTENTION: The format of your rows should be exactly the same as the date format in your computer/BP. To know the exact format, use the Now() function in a calculation stage and evaluate the expression.
 

Mounika_Banda$

New Member
Hello hamzakhan,

Using Utility Collection Manipulation - Filter Collection, your filter should be:
"Column name < '" & Now() & "'"

The single quotes before and after the function are very important!

ATTENTION: The format of your rows should be exactly the same as the date format in your computer/BP. To know the exact format, use the Now() function in a calculation stage and evaluate the expression.


Hi,
I extracted data from excel and stored in a collection.I need to filter collection whose date value is less than today's date.
"'ToDate(Date)' <= 'Today()'" When I use this I get entire collection in filtered collection.Can you suggest a way to fix this?
 

lakshmis

New Member
Hi Gil.Silva,

I have an excel where the column is in text format having dates in mm/dd/yyyy format and in the same column there is some text coming in.
So, I wanted to write a query using oledb to fetch that column having dates older than 9 months.
Could you please help me on this.
 
Top