Hello

i am working on small blog system.

there is following database image :

www.freeimagehosting.net/uploads/218743366b.png

i want to get total number of article for the month of "MAY"

e.g. May(25)

here 25 is total number of articles in the month of "MAY" (like archive)

how to filter month from date???

so how can i get this number:

i have tried the following code to get the total number of article of the month.

    IPredicateExpression filter = new PredicateExpression();
    filter.Add(CmsArticlesFields.DtCreationDate="");
    **in above "" what i have to write so i can get the month from this string 
    5/12/2009 5:09:07 PM**
    CmsArticlesCollection col = new CmsArticlesCollection();
    int i = (int)col.GetDbCount(filter);

can you please give me some example for this

You need to use DBFunctionCall to access the MONTH function of the RDBMS that you are using. There is an example here

Matt

Here's an Example from LLBLGen Pro's Help:

Creates a filter which filters on month = 3

IPredicate monthFilter = new EntityField("OrderMonth", new DbFunctionCall("CAST({0} AS bigint)",
    new object[] { new DbFunctionCall("MONTH", new object[] { OrderFields.OrderDate }) })) == 3;

Hope this helps!

Ryan

rdhatch wrote:

Here's an Example from LLBLGen Pro's Help:

Creates a filter which filters on month = 3

IPredicate monthFilter = new EntityField("OrderMonth", new DbFunctionCall("CAST({0} AS bigint)",
    new object[] { new DbFunctionCall("MONTH", new object[] { OrderFields.OrderDate }) })) == 3;

Hope this helps!

Ryan

hi rdhatch

thanks a lot dude to solve my problem.

but i have one more query, this will gives all record of 3rd month (i.e. MARCH) , can i retrview only 3rd month record of year 2005 only.

only march month record of year 2005

please help again.

MTrinder wrote:

You need to use DBFunctionCall to access the MONTH function of the RDBMS that you are using. There is an example here

Matt

Thanks MTrinder,

your guide also help me.

thanks again.

To filter by Month and Year (3/2005) - you will simply need to add another Predicate for the Year.

So, keep the month filter the way it is. And add another DbFunctionCall filter using the YEAR function of your database. You can call any function your database supports.

Hope this helps!

Ryan

PS. If you want to get a list of all Months & the corresponding number of total articles, please look at Help menu in LLBLGen Pro. Lookup GROUP BY and AGGREGATES. You can create a simple DynamicList in code and populate a DataTable with the totals. This way you're only pulling the totals from the database, instead of pulling every article from the database (which is what happens when you fetch Collection).

rdhatch wrote:

To filter by Month and Year (3/2005) - you will simply need to add another Predicate for the Year.

So, keep the month filter the way it is. And add another DbFunctionCall filter using the YEAR function of your database. You can call any function your database supports.

Hope this helps!

Ryan

PS. If you want to get a list of all Months & the corresponding number of total articles, please look at Help menu in LLBLGen Pro. Lookup GROUP BY and AGGREGATES. You can create a simple DynamicList in code and populate a DataTable with the totals. This way you're only pulling the totals from the database, instead of pulling every article from the database (which is what happens when you fetch Collection).

hi rdhatch,

Thanks for reply

but i am confused how to add two filter here in getdbcount function

IPredicate monthFilter = new EntityField("OrderMonth", new DbFunctionCall("CAST({0} AS bigint)", new object[] { new DbFunctionCall("MONTH", new object[] { OrdersFields.OrderDate }) })) == 3;

IPredicate YearFilter = new EntityField("OrderYear", new DbFunctionCall("CAST({0} AS bigint)", new object[] { new DbFunctionCall("YEAR", new object[] { OrdersFields.OrderDate }) })) == 2005;

OrdersCollection or = new OrdersCollection(); int i = Convert.ToInt32(or.GetDbCount(monthFilter));

You're very close. Predicate Expressions can contain multiple Predicates.

Dim myPredicates as New PredicateExpression
myPredicates.Add(monthFilter)
myPredicates.Add(yearFilter)
...GetDbCount(myPredicates)

Hope this helps!

Ryan

rdhatch wrote:

You're very close. Predicate Expressions can contain multiple Predicates.

Dim myPredicates as New PredicateExpression
myPredicates.Add(monthFilter)
myPredicates.Add(yearFilter)
...GetDbCount(myPredicates)

Hope this helps!

Ryan

Thanks a lot for help.you are great.