Querying Mysql using ODBC

if you aer just trying to display the DAY of the month...
you can do so in reports ...
jst add the field and add an expression to it...there are various date expressions that you can use...

please see here the list of date and time functions of MySQL:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

jaiganeshsrinivasan:

i want to sort the sales based on year,month.therefore for that i am trying to seperate that in the query.so i can drag and drop the year in the table and use group by.
if you know another way please help me.

Angellll:

SELECT     transactions.price, transactions.state, transactions.CheckDate, transactions.BankID, products.EngName, DAYOFMONTH(transactions.CheckDate)
                      AS `Day`, MONTHNAME(transactions.CheckDate) AS Expr1

the DAYOFMONTH function returns the day but when i use MONTHNAME i get <binarydata> instead of the month.

Angellll:

i get an error saying check syntax near (20))


SELECT     transactions.price, transactions.state, transactions.CheckDate, transactions.BankID, products.EngName, DAYOFMONTH(transactions.CheckDate)
                      AS `Day`, MONTHNAME(transactions.CheckDate) AS Expr1, YEARWEEK(transactions.CheckDate) AS Expr2,
                      `CAST`(MONTHNAME(transactions.CheckDate) AS `CHAR`(20)) AS Expr1

in your group (layout --> right click on your group --> edit group)
give the expression as =Year(Fields!d1d.Value) where d1d is your datetimefield...simple

there is no single quote for 'CAST' and 'CHAR' in ANGELS example...
just use CAST / CHAR...

remove the ``around CHAR and CAST

that quote is put automactically by Visual Studio when i press execute

jaiganeshsrinivasan:

let me try your way

jaiganeshsrinivasan:

It worked your way.

thanks everybody for your help