Hi

Help! I just can't find it in the documentation I've found this: https://www.llblgen.com/Documentation/5.6/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_functionmappings.htm#datetime-functions where some function mappings are described - but I can't figure out how to use them.

Consider this simple query var customerProducts = new EntityCollection<CustomerproductEntity>();

        using (var adapter = new DataAccessAdapter())
        {
            var qf = new QueryFactory();
            var q = qf.Customerproduct
                    .WithPath(CustomerproductEntity.PrefetchPathProduct)
                    .Where(CustomerproductFields.CustomerId == 5);
            adapter.FetchQuery(q, customerProducts);
        }

corresponding to

SELECT * FROM customerproducts JOIN products ON customerproducts.productid = products.id WHERE CustomerId = 5

What I would like is to add some date-filtering like this:

AND MONTH(customerproduct.nextinvoicingdate) = ?month AND YEAR(customerproduct.nextinvoicingdate) = ?year

but - I am unable to guess how to use the Year() and Month() functions in the .Where(...) clause of my QuerySpec - and I can't find it There are GreaterThan(...) , Between(...) like this .AndWhere(CustomerproductFields.NextInvoicingDate.GreaterThan(DateTime.Now)) - but I want to compare the specific year part of the datetime in NextInvoicingDate.

Also, I would like to add

AND ((d.date_of_discontinuation IS NULL) OR (d.date_of_discontinuation >= DATE_ADD(customerproduct.nextinvoicingdate, INTERVAL customerproduct.invoiceInterval MONTH))) which would be something like appending .Where(CustomerproductFields.NextInvoicingDate."GetValue().Add(something, something)" > something else) - but probably isnt...

Could someone please provide a simple example for how to actually use the YEAR() and DATE_ADD() functions?

Thanks in advance!

/Jan

LLBLGen 5.6.1, MySQL

You need to use the DateTimeFunctions class.

e.g.

var qf = new QueryFactory();
var q = qf.Order                        
        .Where(DateTimeFunctions.Year(OrderFields.OrderDate).Equal(1996));

Thanks! Maybe a simple example (like the two lines you just provided) close to the table of functions in the documentation would be helpfull. The only other example I've been able to find is inside a .Select(...) in the "whats new i 5.6"

We'll see if we can add some examples to this page: https://www.llblgen.com/Documentation/5.6/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_functionmappings.htm#pre-defined-function-mappings

as the usage is the same for all these functions but it can indeed be unclear.