MySql query Help need!

Question

0.00/5 (No votes)

See more:

Hi Need help on this query. If I run it up and to the 'REPRES' Section it comes back perfectly. The problem is I need to select between those 2 dates in the "date_entered" column as well.

I getting my data from 2 tables named "fine" and "charge" if selects only the data that has "REDIR" and "REPRES" in the 'status' column but if's not filtering out the to select only between those 2 dates.

Please help.

SQL

SELECT fine.case, fine.regnum, fine.issuing_authority, fine.date_entered, charge.amount, fine.status
FROM fine INNER JOIN charge ON fine.case=charge.case
WHERE fine.status = 'REDIR' OR fine.status = 'REPRES'
AND fine.date_entered >= DATE_FORMAT(fine.date_entered, '%d-%m-%Y') = "1/1/2010"
AND fine.date_entered <= DATE_FORMAT(fine.date_entered, '%d-%m-%Y') = "31/12/2010";

This is the result; As you can see the dates has no effect on it.

'1179430141159012', 'ZHD107GP', 'ALBERTON', '9/2/2011', '100', 'REDIR'
'BF80287274110', 'YZD473GP', 'BELFAST', '9/2/2011', '750', 'REDIR'
'1167289141134730', 'VPH261GP', 'ALBERTON', '9/2/2011', '100', 'REDIR'
'0240240055398532', 'YZD461GP', 'JHB', '9/2/2011', '750', 'REDIR'
'0240240054323059', 'ZKW597GP', 'JHB', '9/2/2011', '1500', 'REDIR'
'N80049240120', 'ZWB136GP', 'NELSPRUIT', '9/2/2011', '750', 'REDIR'
'MB80029645105', 'XPT612GP', 'MIDDELBURG', '9/2/2011', '100', 'REDIR'
'0240240057503759', 'XXK353GP', 'JHB', '9/2/2011', '750', 'REDIR'
'0240240054295984', 'YZF152GP', 'JHB', '9/2/2011', '750', 'REDIR'
'0240240054291614', 'SHD923GP', 'JHB', '9/2/2011', '1500', 'REDIR'
'0240240054215571', 'JDG518GP', 'JHB', '9/2/2011', '750', 'REDIR'
'0240240055373045', 'SJV048GP', 'JHB', '9/2/2011', '1500', 'REDIR'
'0240240055326163', 'DDJ186GP', 'JHB', '9/2/2011', '750', 'REDIR'
'0240240055404932', 'SJV048GP', 'JHB', '15/3/2011', '4500', 'REDIR'

Thank you. Any help will be appreciated.

:-)

Posted 30-Aug-11 5:05am

Dirk C De Winnaar

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Yogesh Pednekar · Answer 1 · 2011-08-30T08:55:00

Solution 3

your query should be as follow

SQL

SELECT fine.case, fine.regnum, fine.issuing_authority, fine.date_entered, charge.amount, fine.status
FROM fine INNER JOIN charge ON fine.case=charge.case
WHERE fine.status in ('REDIR',  'REPRES')
AND fine.date_entered between DATE_FORMAT(fine.date_entered, '%d-%m-%Y') = "1/1/2010"
AND DATE_FORMAT(fine.date_entered, '%d-%m-%Y') = "31/12/2010";

Try this

Posted 30-Aug-11 8:55am

Yogesh Pednekar

Comments

Dirk C De Winnaar 30-Aug-11 15:11pm

Error Code: 1064. 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 '= "1/1/2010"
AND DATE_FORMAT(fine.date_entered, '%d-%m-%Y') = "31/12/2010"' at line 4

The second "=" sign is not accepted.

RaisKazi · Answer 2 · 2011-08-30T05:19:00

Solution 1

MySQL follows "YYYY-MM-DD" format as default for DateTime.
http://dev.mysql.com/doc/refman/5.0/en/datetime.html

Try by modifying your query as below.

SQL

SELECT fine.case, fine.regnum, fine.issuing_authority, fine.date_entered, charge.amount, fine.status
FROM fine INNER JOIN charge ON fine.case=charge.case
WHERE fine.status = 'REDIR' OR fine.status = 'REPRES'
AND (fine.date_entered BETWEEN '2010-1-1' AND '2010-12-31');

Edited -

SQL

SELECT fine.case, fine.regnum, fine.issuing_authority, fine.date_entered, charge.amount, fine.status
FROM fine INNER JOIN charge ON fine.case=charge.case
WHERE (fine.status = 'REDIR' OR fine.status = 'REPRES')
AND (fine.date_entered BETWEEN '1-1-2010' AND '31-12-2010');

Posted 30-Aug-11 5:19am

RaisKazi

Updated 30-Aug-11 5:33am

v2

Comments

Dirk C De Winnaar 30-Aug-11 11:31am

Hi I know the date format of MySql is yyyy-mm-dd the date inside the table is dd/MM/yyyy.
Query still don't work.

RaisKazi 30-Aug-11 11:35am

Have a look at updated query. Observe the Parenthisis used in where clause.

Dirk C De Winnaar 30-Aug-11 12:31pm

Hi RaisKazi. It still does not work. It filters all the needed data but all the other dates from 2011 , 2009, 2010 even if I change the month part to order between the month of '5' eg. It show everything not just that month.

RaisKazi 30-Aug-11 12:56pm

Ok. Can you change '1-1-2010' AND '31-12-2010' to '1/1/2010' AND '31/12/2010'.
Also, I assume "date_entered" column has DateTime Datatype.

Dirk C De Winnaar 30-Aug-11 13:11pm

Hi I still don't work. date_entered does not have the DT datatype. It's not a DB I designed. It's another one I'm writting an addition to.
Sorry should have mentioned it before.
:-)

Dirk C De Winnaar 30-Aug-11 13:21pm

It's CHAR(10)

RaisKazi 30-Aug-11 13:34pm

Firstly execute select CONVERT(fine.date_entered, Date) from fine;

Check the result, Its having "-" Or "/" as a separator. Modify separator in below query based on the result of above query, And then execute below query.



SELECT fine.case, fine.regnum, fine.issuing_authority, fine.date_entered, charge.amount, fine.status

FROM fine INNER JOIN charge ON fine.case=charge.case

WHERE (fine.status = 'REDIR' OR fine.status = 'REPRES')

AND (CONVERT(fine.date_entered, Date) BETWEEN '1-1-2010' AND '31-12-2010');

Dirk C De Winnaar 30-Aug-11 13:43pm

I did the first select to convert. and changed the "/" separator. Nothing shows now if I execute.

Dirk C De Winnaar 30-Aug-11 13:51pm

Please Note that I also can't change to DataType of this column as the original program still needs to work off it I'm using TEST DB now, so it's ok, My Addition just needs to get this Data back from the MySql DB.

RaisKazi 30-Aug-11 13:58pm

Alright, Try as below.



SELECT fine.case, fine.regnum, fine.issuing_authority, fine.date_entered, charge.amount, fine.status

FROM fine INNER JOIN charge ON fine.case=charge.case

WHERE (fine.status = 'REDIR' OR fine.status = 'REPRES')

AND (CONVERT(fine.date_entered, Date) BETWEEN CONVERT('1/1/2010', Date) AND CONVERT('31/12/2010', Date) );

Dirk C De Winnaar 30-Aug-11 14:02pm

Nothing. No Results.

Dirk C De Winnaar 30-Aug-11 14:53pm

It Return nothing.