Member Avatar for 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

  • 4 Contributors
  • 8 Replies
  • 117 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by smantscheff

Dani AI

Generated

Root cause: approval_date is stored as text in dd/mm/yyyy form, so MySQL compares the strings lexicographically rather than as calendar dates. That is why rows from 2009/2010 still appear even though the date-range seems correct in human terms. As noted, using ISO date text only helps when the column is actually a DATE; correctly pointed out string-vs-date comparison and fixed the substring approach — but rearranging substrings is fragile. Converting to a real DATE is the robust solution.

A quick, non-destructive filter (no schema change) converts the text to DATE at query time and compares that value:

SELECT approval_date
FROM itemdetail
WHERE STR_TO_DATE(approval_date, '%d/%m/%Y')
      BETWEEN '2011-01-01' AND '2011-01-15';

To inspect how strings are parsed and find bad rows:

SELECT approval_date, STR_TO_DATE(approval_date, '%d/%m/%Y') AS parsed
FROM itemdetail
ORDER BY parsed;

For a permanent fix (better performance, allow indexing, avoid repeated conversions), add a DATE column, populate it from the VARCHAR values, verify, then replace the old column:

ALTER TABLE itemdetail ADD COLUMN approval_date_dt DATE;
UPDATE itemdetail
SET approval_date_dt = STR_TO_DATE(approval_date, '%d/%m/%Y');
SELECT approval_date FROM itemdetail
WHERE approval_date_dt IS NULL AND approval_date <> '';
-- after verification:
ALTER TABLE itemdetail DROP COLUMN approval_date;
ALTER TABLE itemdetail CHANGE approval_date_dt approval_date DATE;
CREATE INDEX idx_approval_date ON itemdetail (approval_date);

Notes: BETWEEN is inclusive; for DATETIME end-of-day boundaries may need explicit time or use < (end_date + INTERVAL 1 DAY). Back up the table before schema changes. See the MySQL STR_TO_DATE function and ALTER TABLE documentation for details: STR_TO_DATE, ALTER TABLE.

Recommended Answers

All 8 Replies

Member Avatar for kvprajapati

Try this,

SELECT approval_date
FROM itemdetail
WHERE approval_date BETWEEN '2011/01/01' AND '2011/01/15'
Member Avatar for 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

Member Avatar for kvprajapati

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

For correct output datatype must be date

Member Avatar for 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)

Member Avatar for 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

Member Avatar for 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.

Edited by pritaeas because: n/a
Member Avatar for 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

Member Avatar for 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';
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.