Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?
Course Of The Month
7 days, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
MySQL DateTime Query Problem
Posted on 2010-09-03
If I do a query in the MySQL workbench, I get 8 records (which is correct), but when I do the same query in my program, I only get the 1st 2 records. The only thing I'm really querying against is a tblARInvoice.Invoice_Date datetime field.
txtYY.text contains the last 2 digits of the year. ex 10 for 2010
txtMM.text contains 2 digits for the month. ex 09
txtDD.text contains 2 digits for the day. ex 03
The majority of the select statement is below.
What am I doing wrong to get different results?
txtYY.text contains the last 2 digits of the year. ex 10 for 2010
txtMM.text contains 2 digits for the month. ex 09
txtDD.text contains 2 digits for the day. ex 03
The majority of the select statement is below.
What am I doing wrong to get different results?
SELECT
Clients.FNAME, tblARInvoice.SEQ, tblARInvoice.Invoice_Date AS 'INVOICE_DATE', tblARInvoice.YEAR, tblARInvoice.strStatus AS 'STATUS', tblARReceipts.DATE as 'RECEIPT DATE'
FROM tblARInvoice INNER JOIN Clients ON tblARInvoice.strClientSIN = Clients.SIN LEFT OUTER JOIN tblARReceipts ON tblARInvoice.strINVOICE_NUMBER = tblARReceipts.strInvoiceNumber
WHERE (tblARInvoice.Invoice_Date <= '20" & txtYY.Text & "-" & txtMM.Text & "-" & txtDD.Text & "') ORDER BY tblARReceipts.DATE, Clients.LNAME, Clients.FNAME
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
14
10
2- +1
27 Comments
can you show the code that runs this query?
also, can you output the SQL string value before it's executed?
also, can you output the SQL string value before it's executed?
Active today
I sure can angellll
The string value is :
SELECT
Clients.FNAME, tblARInvoice.SEQ, tblARInvoice.Invoice_Date AS 'INVOICE_DATE', tblARInvoice.YEAR, tblARInvoice.strStatus AS 'STATUS', tblARReceipts.DATE as 'RECEIPT DATE' FROM tblARInvoice INNER JOIN Clients ON tblARInvoice.strClientSIN = Clients.SIN LEFT OUTER JOIN tblARReceipts ON tblARInvoice.strINVOICE_NU
WHERE (tblARInvoice.Invoice_Date
ORDER BY tblARReceipts.DATE, Clients.LNAME, Clients.FNAME
The code that runs the query is below:
The string value is :
SELECT
Clients.FNAME, tblARInvoice.SEQ, tblARInvoice.Invoice_Date AS 'INVOICE_DATE', tblARInvoice.YEAR, tblARInvoice.strStatus AS 'STATUS', tblARReceipts.DATE as 'RECEIPT DATE' FROM tblARInvoice INNER JOIN Clients ON tblARInvoice.strClientSIN = Clients.SIN LEFT OUTER JOIN tblARReceipts ON tblARInvoice.strINVOICE_NU
WHERE (tblARInvoice.Invoice_Date
ORDER BY tblARReceipts.DATE, Clients.LNAME, Clients.FNAME
The code that runs the query is below:
myConnection = New MySqlConnection(frmLogin.strConnection)
myConnection.Open()
Dim daReportARInvoice As New MySqlDataAdapter("SELECT Clients.FNAME, tblARInvoice.SEQ, tblARInvoice.Invoice_Date AS 'INVOICE_DATE', tblARInvoice.YEAR, tblARInvoice.strStatus AS 'STATUS', tblARReceipts.DATE as 'RECEIPT DATE' FROM tblARInvoice INNER JOIN Clients ON tblARInvoice.strClientSIN = Clients.SIN LEFT OUTER JOIN tblARReceipts ON tblARInvoice.strINVOICE_NUMBER = tblARReceipts.strInvoiceNumber WHERE (tblARInvoice.Invoice_Date <= '20" & txtYY.Text & "-" & txtMM.Text & "-" & txtDD.Text & "') ORDER BY tblARReceipts.DATE, Clients.LNAME, Clients.FNAME", myConnection)
daReportARInvoice.Fill(dsReportARInvoice)
myConnection.Close()
Convert string to date
str_to_date('10-10-2006', "%d-%m-%Y");
str_to_date('10-10-2006', "%d-%m-%Y");
SELECT
Clients.FNAME,
tblARInvoice.SEQ,
tblARInvoice.Invoice_Date AS 'INVOICE_DATE',
tblARInvoice.YEAR,
tblARInvoice.strStatus AS 'STATUS',
tblARReceipts.DATE as 'RECEIPT DATE'
FROM tblARInvoice
INNER JOIN Clients ON tblARInvoice.strClientSIN = Clients.SIN
LEFT OUTER JOIN tblARReceipts ON tblARInvoice.strINVOICE_NUMBER = tblARReceipts.strInvoiceNumber
WHERE (tblARInvoice.Invoice_Date <= str_to_date('" & txtDD.Text & "-" & txtMM.Text & "-20" & txtYY.Text & "', "%d-%m-%Y")) ORDER BY tblARReceipts.DATE, Clients.LNAME, Clients.FNAME
Active today
How can I double check?
I have 2 MySQL databases and they contain identical information in both databases.
I have 2 MySQL databases and they contain identical information in both databases.
Active today
If I try to do the query with db1.tablename.columnnames it works but if I try to do it with db2.tablename.columnnames,
Active today
Let me rephrase myself:
In WorkBench, if I was to select either of the two identical databases (db1.table or db2.table) I get all 8 rows, which is correct.
If I use db1 in my program, I get 2 rows.
If I use db2 in my program, it doesn't work. I would believe this is because the connection string is pointing db1 and not db2.
In WorkBench, if I was to select either of the two identical databases (db1.table or db2.table) I get all 8 rows, which is correct.
If I use db1 in my program, I get 2 rows.
If I use db2 in my program, it doesn't work. I would believe this is because the connection string is pointing db1 and not db2.
>If I use db2 in my program, it doesn't work.
what error?
apart from that, I see no explanation on this "different behaviour"
what error?
apart from that, I see no explanation on this "different behaviour"
Active today
Another note:
In my connection string I have respect binary flags=false;
I have this because when I was doing queries I would get System.Byte[] except of the string values.
Would this possibly cause my problem?
In my connection string I have respect binary flags=false;
I have this because when I was doing queries I would get System.Byte[] except of the string values.
Would this possibly cause my problem?
Active today
My error is "Unknown column 'db2.clients.lname' in 'field list'"
There are no columns that are binary or blob.
There are no columns that are binary or blob.
>My error is "Unknown column 'db2.clients.lname' in 'field list'"
your query is written incorrectly, then...
you should use table aliases in your queries, like:
your query is written incorrectly, then...
you should use table aliases in your queries, like:
myConnection = New MySqlConnection(frmLogin.strConnection)
myConnection.Open()
Dim daReportARInvoice As New MySqlDataAdapter("SELECT c.FNAME, i.SEQ, i.Invoice_Date AS `INVOICE_DATE`, i.`YEAR`, i.strStatus AS `STATUS`, r.`DATE` as `RECEIPT DATE` FROM tblARInvoice i INNER JOIN Clients c ON i.strClientSIN = c.SIN LEFT OUTER JOIN tblARReceipts r ON i.strINVOICE_NUMBER = r.strInvoiceNumber WHERE (i.Invoice_Date <= '20" & txtYY.Text & "-" & txtMM.Text & "-" & txtDD.Text & "') ORDER BY r.`DATE`, c.LNAME, c.FNAME ", myConnection)
daReportARInvoice.Fill(dsReportARInvoice)
myConnection.Close()
so, if you now had to specify a db name in the query:
myConnection = New MySqlConnection(frmLogin.strConnection)
myConnection.Open()
Dim daReportARInvoice As New MySqlDataAdapter("SELECT c.FNAME, i.SEQ, i.Invoice_Date AS `INVOICE_DATE`, i.`YEAR`, i.strStatus AS `STATUS`, r.`DATE` as `RECEIPT DATE` FROM db2.tblARInvoice i INNER JOIN db2.Clients c ON i.strClientSIN = c.SIN LEFT OUTER JOIN db2.tblARReceipts r ON i.strINVOICE_NUMBER = r.strInvoiceNumber WHERE (i.Invoice_Date <= '20" & txtYY.Text & "-" & txtMM.Text & "-" & txtDD.Text & "') ORDER BY r.`DATE`, c.LNAME, c.FNAME ", myConnection)
daReportARInvoice.Fill(dsReportARInvoice)
myConnection.Close()
finally, it could be permission problems ... (the error message). ..
but 2 records returned instead of 8 ... can only data issues.
but 2 records returned instead of 8 ... can only data issues.
Active today
I agree. That is the only thing that makes sense to me is it being data related, but I don't know where it could be. My lack of experience with MySQL doesn't help any. I'm use to MS SQL.
Do you know of any possible tests I can do that might give us more information on what is causing this?
Do you know of any possible tests I can do that might give us more information on what is causing this?
reduce the queries to see as from when it returns to "normal" ...
or start with a minimum query, and add up conditions/joins to see as from when it starts going wrong ...
or start with a minimum query, and add up conditions/joins to see as from when it starts going wrong ...
Active today
I'm not quite sure what I did cause I've tried a number of things to get this to work.
The last thing I attempted to do was select put in db1. in front of all column names, and it worked. I get all 8 results.
I assumed it would automatically select the database that is mentioned in the connection string. On top of that, the two databases are identical so I don't understand on why mentioning the name of the database that is mentioned in the connection string would do anything to change the query results.
Any idea what could have caused this?
The last thing I attempted to do was select put in db1. in front of all column names, and it worked. I get all 8 results.
I assumed it would automatically select the database that is mentioned in the connection string. On top of that, the two databases are identical so I don't understand on why mentioning the name of the database that is mentioned in the connection string would do anything to change the query results.
Any idea what could have caused this?
Active today
My Mistake!! This isn't over yet!
I accidently removed the invoice_date portion of the where statement. If I remove the "where arinvoice.invoice_date ... ' portion of the where statement, I get the correct results.
If I keep that portion in, I get no records (where previously I got 2 records).
I accidently removed the invoice_date portion of the where statement. If I remove the "where arinvoice.invoice_date ... ' portion of the where statement, I get the correct results.
If I keep that portion in, I get no records (where previously I got 2 records).
Featured Post
What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
- Query Syntax
- SQL
- Databases
- MySQL Server
- MongoDB
- NoSQL Databases, Percona
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
- Superb Internet
- IT Administration
- Ransomware
- Storage Software
- Databases
- Cloud Computing, Cloud Services, *Backups, *Online Backup Services, *Backup Software
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have.
In this…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, 19 hours left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.