2

I am storing data in varchar (date + additional information). I want to filter data in between range.

For Example:

I have datesubact column like below.

2013/07/01,S
2013/07/26,A
2013/07/05,S
2013/07/06,S
.
.

I want to filter the range between 2013/07/01 to 2013/07/06. How do I put Mysql query for this?... Please anyone help me...

Improve this question
1
  • between '2013/07/01' AND '2013/07/06' I would think? Commented Aug 9, 2013 at 13:39

3 Answers 3

Reset to default
4

Never, never, never store multiple values in one column!

Like you see now this will only give you headaches. Normalize your table.

It should be

date_column (type = date) | other_column (type = char(1))
--------------------------+------------------------------
2013/07/01                |    S
2013/07/26                |    A
2013/07/05                |    S
2013/07/06                |    S
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

By Normalizing the table(spliting 2 columns), I have to modify several pages Mr.juergen d. Is there any other way to fix this by query?
Better to fix several pages and plan your design properly now than having to change hundreds later. Do as he says, it's the correct method. Stop being lazy.
0

This should work 

SELECT  * from Tbl WHERE  date('2013/07/01,S')  BETWEEN @startDate and @EndDate  ;

see this SQLFiddle

Improve this answer

4 Comments

It has been voted down because you gave an answer to an incorrect situation. It shows that you do not know the concept of normalized databases. Almost every developer stresses on the fact that all databases should be normalized. You have just solved one of Biju's problems and created him many more.
@ClaireG ..fine .. but the OP was not asked how to normalize the table .. but he want a solution to query his table .. Further the table is already designed and populated with the data
Yes you are right, but it is easier to spend fifteen minutes fixing a fatal mistake rather than creating several more. If he / she were to follow our suggestions he / she would have not only fixed an error, but also made it easier for him / herself and other developers working on the same database to query the table in question. If this was an answer to an assignment, he / she would get the answer wrong because even tutors emphasis on the fact that normalized databases are a must. It is up to him / her though :)
Ok ClaireG ... I Agree.
0

Divide the dates and additional information and put them in separate columns. After that you can query like the following:

SELECT _yourColumnName FROM tbl_yourTableName
WHERE _date
BETWEEN '2013/07/01' AND '2013/07/06'

Do not ever leave multiple values in a single column. It will only cause problems for you and for those who will have their work based on your own.

Improve this answer

2 Comments

By Normalizing the table(spliting 2 columns), I have to modify several pages ClaireG. Is there any other way to fix this by query?
I suggest you modify those pages and normalize your database as the rest of the posters suggested as well. It is not only the best practice to do so, but also saves you much more frustration later on. Trust me, unnormalized databases only give you more problems, and thus more work.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.