MySQL select query return unrelated output

Question

SELECT updateDate, id,cSsn,cLname,cFname,cDOB,cDOD,cCity,cState,cHospital,cCurstatus,cMmrcashworker,cTelephone FROM med_patient WHERE cCurstatus!='completed' AND cMmrcashworker = '2' AND cHospital = '1234' OR cHospital1 = '1234' OR cHospital2 ='1234' AND updateDate between '1/30/2010' and '1/28/2010' order by id desc'

Output:

updateDate   cHospital   cHospital1     cHospital2

01/15/2010   1234

01/15/2010   1234

But acutally to my knowledge my query must return an empty row.

Where is the mistake in the query?

jspcal · Accepted Answer · 2010-01-17 08:03:43Z

1

try adding some parentheses to your cHospital condition, otherwise the or will not be what you expect:

cCurstatus!='completed' AND cMmrcashworker = '2' AND
(cHospital = '1234' OR cHospital1 = '1234' OR cHospital2 ='1234') AND
updateDate between '1/30/2010' and '1/28/2010' order by id desc'

also:

... updateDate between '1/30/2010' and '1/28/2010' ...

^ that is doing a string compare rather than a date comparison, try changing to:

... updateDate between '2010-01-30' and '2010-01-28' ...

edited Jan 17, 2010 at 8:03

answered Jan 17, 2010 at 7:29

jspcal

52k7 gold badges76 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Bharanikumar Over a year ago

in my database table , am maintaining like 1/30/2010 , is it must i have to change the format.. else is there any alternate solution for my problem..

jspcal Over a year ago

your db has values like '01/15/2010', so you will need to compare based on mm/dd/yyyy, which could be done as a string or date

Bharanikumar Over a year ago

<pre> SELECT updateDate,cHospital,cHospital1,cHospital2 FROM med_patient WHERE cCurstatus!='completed' AND cMmrcashworker = '2' AND (cHospital = '1234' OR cHospital1 = '1234' OR cHospital2 ='1234') AND updateDate between '2010-01-1' and '2010-01-28' order by id desc </pre> As per your guidence , i have changed dateformat to 2010-01-28 , I have one patient entry, one of my patient updateDate is 2010-01-17 , but my above query return empty row , Why ?

Bharanikumar Over a year ago

one thing i want to convey , as per my database design my updateDate datatype is varchar ,

jspcal Over a year ago

check the other values to see if they match... if you're changing your column type, probably best to make it a datetime (also '2010-01-1' looks like a typo, should be '2010-01-01')

|

Peter Mortensen · Accepted Answer · 2011-03-10 20:40:12Z

0

My problem is fixed. As per jspcal's guidance, I changed my datetype to datetime.

Now I get the expected output.

edited Mar 10, 2011 at 20:40

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Jan 17, 2010 at 9:34

Bharanikumar

25.8k51 gold badges135 silver badges203 bronze badges

Collectives™ on Stack Overflow

MySQL select query return unrelated output

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related