Question

How to use CreateMonthFunction, CreateYearFunction in filter ESQ Server Side?

07:19 Apr 14, 2022

Hi everyone,

how to use filter month and year Esq Server (EntitySchemaQuery)?

SELECT 
* 
FROM UsrTable 
WHERE 
MONTH(CreatedOn) = 1 AND YEAR(CreatedOn) = 2022

I found there is a function at https://academy.creatio.com/api/netcoreapi/7.17.0/#Terrasoft.Core~Terrasoft.Core.Entities.EntitySchemaQuery~CreateMonthFunction.html for get month and year.

https://prnt.sc/8brMGtYq3qhP

But how to use in filter?

esq.Filters.Add(esq.CreateFilterWithParameters(FilterComparisonType.Equal, "CreatedOn", Month));
esq.Filters.Add(esq.CreateFilterWithParameters(FilterComparisonType.Equal, "CreatedOn", Year));

Thank you.

1 comments

Best reply

Ryan Farley

18:16 Apr 14, 2022

Hello Romadan,

You can do filters like this using macros:

// CreatedOn is the 1st month (January)
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.Month, 1));
 
// CreatedOn is the first day of the month
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.DayOfMonth, 1));
 
// CreatedOn is in the year 2022
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.Year, 2022));

Ryan

Ryan Farley

18:16 Apr 14, 2022

Hello Romadan,

You can do filters like this using macros:

// CreatedOn is the 1st month (January)
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.Month, 1));
 
// CreatedOn is the first day of the month
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.DayOfMonth, 1));
 
// CreatedOn is in the year 2022
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.Year, 2022));

Ryan

Show all comments