[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

Querying Mysql using ODBC

Hello there,

I am using SSRS to create reports.the db i am using is MySQL.so I had to use ODBC connection.now in my mysql table i have a column date from which i want to remove the year,month part.but the problem is it does not like DatePart function of mssql. also when i use mysql function i get error.please help

cheers
zolf
0
zolf
Asked:
zolf
  • 4
  • 4
  • 3
2 Solutions
 
Jai STech ArchCommented:
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...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
zolfAuthor Commented:

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.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:

SELECT     transactions.price, transactions.state, transactions.CheckDate, transactions.BankID, products.EngName, DAYOFMONTH(transactions.CheckDate)
                      AS `Day`, CAST( MONTHNAME(transactions.CheckDate) As CHAR(20)) AS Expr1
0
 
Jai STech ArchCommented:
can you just select the date and while grouping in SSRS you group by the YEAR(youdate) rather than your date...i will check an example for you and will come back...
0
 
zolfAuthor Commented:

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
0
 
Jai STech ArchCommented:
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
0
 
Jai STech ArchCommented:
there is no single quote for 'CAST' and 'CHAR' in ANGELS example...
just use CAST / CHAR...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
remove the ``around CHAR and CAST
0
 
zolfAuthor Commented:

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

jaiganeshsrinivasan:

let me try your way

0
 
zolfAuthor Commented:

jaiganeshsrinivasan:

It worked your way.

thanks everybody for your help
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now