Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> How to use YEAR and DATE_ADD
 

Pages: 1
LLBLGen Pro Runtime Framework
How to use YEAR and DATE_ADD
Page:1/1 

  Print all messages in this thread  
Poster Message
JanRHansen
User



Location:

Joined on:
02-Jan-2019 16:48:19
Posted:
11 posts
# Posted on: 02-Jan-2020 17:06:45.  
Hi

Help! I just can't find it in the documentation Regular Smiley
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 Regular Smiley 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... Regular Smiley

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
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14567 posts
# Posted on: 02-Jan-2020 21:24:20.  
You need to use the DateTimeFunctions class.

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



  Top
JanRHansen
User



Location:

Joined on:
02-Jan-2019 16:48:19
Posted:
11 posts
# Posted on: 03-Jan-2020 14:13:08.  
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" Regular Smiley
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37784 posts
# Posted on: 03-Jan-2020 15:55:02.  
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.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.