Here i have sample code, in which i was using MS SQL Server

IPredicate monthFilter = new EntityField("OrderMonth", new DbFunctionCall("CAST({0} AS bigint)",
                           new object[] { new DbFunctionCall("MONTH", new object[] { CmsArticlesFields.DtCreationDate }) })) == i;
                IPredicate yearFilter = new EntityField("OrderMonth", new DbFunctionCall("CAST({0} AS bigint)",
                           new object[] { new DbFunctionCall("YEAR", new object[] { CmsArticlesFields.DtCreationDate }) })) == year;

but now i am using MY SQL, so it throw error like this :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bigint) = 1 AND CAST(YEAR(cms_articles.dtCreationDate) AS bigint) = 2009))) ' at line 1

Total = Convert.ToInt32(art.GetDbCount(dateFilter));

My Full Code Like this :

IPredicate monthFilter = new EntityField("OrderMonth", new DbFunctionCall("CAST({0} AS bigint)",
                           new object[] { new DbFunctionCall("MONTH", new object[] { CmsArticlesFields.DtCreationDate }) })) == i;
                IPredicate yearFilter = new EntityField("OrderMonth", new DbFunctionCall("CAST({0} AS bigint)",
                           new object[] { new DbFunctionCall("YEAR", new object[] { CmsArticlesFields.DtCreationDate }) })) == year;
                PredicateExpression dateFilter = new PredicateExpression();
                dateFilter.Add(monthFilter);
                dateFilter.Add(yearFilter);
                
                
                Total = Convert.ToInt32(art.GetDbCount(dateFilter));

is DB Function is different than MS SQL ??

please help me to solve this problem

CAST, YEAR & MONTH.... are all SQL Server functions, does MySQL have the exact similar function? I think not...

It has YEAR and CAST. If you want to be certain, check the MySql documentation, it's rather good

Otis wrote:

It has YEAR and CAST. If you want to be certain, check the MySql documentation, it's rather good

Still i am getting error :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TINYINT) = 1 AND CAST(YEAR(cms_articles.dtCreationDate) AS TINYINT) = 2009))' at line 1

Error on this line : Total = Convert.ToInt32(art.GetDbCount(dateFilter));

IPredicate monthFilter = new EntityField("dtCreationMonth", new DbFunctionCall("CAST({0} AS TINYINT)",
                           new object[] { new DbFunctionCall("MONTH", new object[] { CmsArticlesFields.DtCreationDate }) })) == i;
                IPredicate yearFilter = new EntityField("dtCreationYear", new DbFunctionCall("CAST({0} AS TINYINT)",
                           new object[] { new DbFunctionCall("YEAR", new object[] { CmsArticlesFields.DtCreationDate }) })) == year;
                PredicateExpression dateFilter = new PredicateExpression();
                dateFilter.Add(monthFilter);
                dateFilter.Add(yearFilter);
                
                
                Total = Convert.ToInt32(art.GetDbCount(dateFilter));

i have refer this link. in Mysql has same Function as MS SQL has : MONTH,YEAR i don't understand where i am wrong??

The CAST function in MySQL can cast to the following types:

The CONVERT() and CAST() functions take a value of one type and produce a value of another type. The type can be one of the following values: ■BINARY[(N)] ■CHAR[(N)] ■DATE ■DATETIME ■DECIMAL[(M[,D])] ■SIGNED [INTEGER] ■TIME ■UNSIGNED [INTEGER]

ref: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

That's why you get an error casting to TINYINT.

btw, why are you casting anyway? Month and Year functions will return numbers that you can use directly in your filter.

Walaa wrote:

The CAST function in MySQL can cast to the following types:

The CONVERT() and CAST() functions take a value of one type and produce a value of another type. The type can be one of the following values: ■BINARY[(N)] ■CHAR[(N)] ■DATE ■DATETIME ■DECIMAL[(M[,D])] ■SIGNED [INTEGER] ■TIME ■UNSIGNED [INTEGER]

ref: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

That's why you get an error casting to TINYINT.

btw, why are you casting anyway? Month and Year functions will return numbers that you can use directly in your filter.

Thanks walaa,

for such good information. this will solve my problem.

thanks a lot again.