Need Help

[derozza]

I experiencing problem in executing query on between date command. I got wrong result.

SELECT approval_date
FROM itemdetail
WHERE approval_date BETWEEN '01/01/2011' AND '15/01/2011'

My Record in Mysql Database:

|Approval Date|
05/01/2011
10/12/2010
12/12/2010
15/12/2010
10/01/2011

|Result|
Like above

Need result
|Approval Date|
05/01/2011
10/10/2011

Anyone could help me. how to get the needed result. sorry am still new in mysql.

Thanks

[kvprajapati]

Try this,

SELECT approval_date
FROM itemdetail
WHERE approval_date BETWEEN '2011/01/01' AND '2011/01/15'

[derozza]

Hi thank for the reply. i still got error on my result. for you information i'm using mysql and approval_date is varchar datatype.

Please help me.

Thanks

[kvprajapati]

> i'm using mysql and approval_date is varchar datatype.

For correct output datatype must be date

[smantscheff]

You are applying the between operator on a character field. Therefore it does not compare dates, but literal strings.
To compare dates on your data you have either to convert them to date fields or to reverse the sequence of the elements.
Try:

SELECT approval_date
FROM itemdetail
WHERE concat(substr(approval_date,6,4),'/',substr(approval_date),4,2),'/',substr(approval_date,1,2)) BETWEEN '2011/01/01' AND '2011/01/15'

(not tested)

[derozza]

You are applying the between operator on a character field. Therefore it does not compare dates, but literal strings.
To compare dates on your data you have either to convert them to date fields or to reverse the sequence of the elements.
Try:

SELECT approval_date
FROM itemdetail
WHERE concat(substr(approval_date,6,4),'/',substr(approval_date),4,2),'/',substr(approval_date,1,2)) BETWEEN '2011/01/01' AND '2011/01/15'

(not tested)

Thanks sir. I tested the query which got error on the syntax. I'm new in sql please help me. i have been this matter since last Thursday.

Thanks

[pritaeas]

Try the following:

SELECT approval_date
FROM itemdetail
WHERE CONCAT(substr(approval_date,6,4),'/',substr(approval_date,4,2),'/',substr(approval_date,1,2)) BETWEEN '2011/01/01' AND '2011/01/15'

Small typo in the previous post I think.

[derozza]

Thanks for d help. But i dont know. The result appear still not filter. My query show all the result. Not from 01/01/2011 to 15/01/2011. the result from 2009 still appear.

Anything that i can do please.

Thanks in advance

[smantscheff]

Are you sure this is the right job for you? Try it with debugging and get to the solution:

SELECT approval_date
FROM itemdetail
WHERE CONCAT(substr(approval_date,7,4),'/',substr(approval_date,4,2),'/',substr(approval_date,1,2)) BETWEEN '2011/01/01' AND '2011/01/15';