Query to get data in between two dates in (type is varchar and formate is dd/mm/yyyy) mysql

Question

1.00/5 (2 votes)

See more:

Hi all...
i have one table like
no Date
1 13/11/2011
2 14/11/2011
3 17/11/2011
4 02/12/2011
5 05/12/2011
and i written one query like
select * from tablname where date between '13/11/2011' and '17/11/2011'
it returns
no date
1 13/11/2011
2 14/11/2011
3 17/11/2011
when i used this query
select * from tablname where date between '13/11/2011' and '05/12/2011'
it wont return any value
because date is stored in varchar and format is dd/mm/yyyy and it is taking and comparing with first two values
soo how can i get the values in between two dates
Please can any one help me
thanks in advance

Posted 12-May-11 21:34pm

tulasiram3975

Updated 12-Sep-17 22:56pm

Add a Solution

5 solutions

Solution 4

You should go with the software changes. Never keep a known bug.

If you have a better solution then do not go for some wrong.

In .net you just change all the references and database call and change the queries that's it.

Keep reading basic and start your projects with all basic known factor that may not create problem in future development.

You can fool your client but not yourself. you must learn with your own mistake and never repeat the same.

So don't afraid to make the changes in database for varchar to datetime datatype.

Just do it....Build a perfect software ;)

Posted 12-May-11 22:52pm

Ra-one

Updated 13-May-11 0:26am

v5

Comments

tulasiram3975 13-May-11 5:53am

Thanks For Suggestion...

Solution 2

First of all why u stored dates as string in database..never do that
To solve ur problem, U have to first convert ur date in datetime in database and then u compare like

C++

convert(datetime,'12/11/2008')

Posted 12-May-11 21:44pm

Ashishmau

Solution 3

I dont know y u created a column to store date in varchar

anyways if you have done so
I would go for creating a view from your table with an additional column that converts your varchar dates in a properly parsed dates.

Then I would do all my queries to that view. this will make your life easier in long run.

Posted 12-May-11 21:45pm

saxenaabhi6

v2

Solution 5

select * from tablename where date between format('13/12/2012','dd/mm/yyyy') and format('17/12/2012','dd/mm/yyyy');

Posted 27-Dec-12 23:50pm

sunnychadha0548

Add a Solution

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

OriginalGriff · Accepted Answer · 2011-05-12T21:39:00

Solution 1

That really isn't easy: You shouldn't store dates in string fields, because it makes this problem difficult.
SQL Dates are always yyyy-MM-dd, not dd/MM/yyyy so you can't even convert these to a date type first, without using multiple SUBSTRING functions and a lot of luck.

Seriously: change your database. Store your dates in Date fields, and you can manipulate them either inside the database or outside a lot more easily, and a lot more flexibly: for example, you do not then have to concern yourself about the users locale and his date format!

"Sir,
Can You Give A Sample Query For Above Table So That I May Proceed I tried Many Ways But Am Not Getting"

It's not complex: try this to give you a clue:

SQL

SELECT CAST((SUBSTRING(Date, 7, 4) + '-' + SUBSTRING(Date, 4, 2) + '-' + SUBSTRING(Date, 1, 2)) AS Date) AS d FROM myTable

Seriously: Save yourself a LOT of future trouble by converting your database to Date or DateTime instead!

Posted 12-May-11 21:39pm

OriginalGriff

Updated 12-May-11 23:54pm

v3

Comments

tulasiram3975 13-May-11 3:52am

The Problem Is I developed My Software Already I dont want to Change if changes happen it takes lots of risk for me convertion the type may solves the problem i know but i need to do with out changs

OriginalGriff 13-May-11 4:03am

If you can't change the database (and I still strongly recommend it) then you will have to rip the data apart and reassemble it as part of your SQL SELECT statement: you may find it easier to do it as a Stored Procedure instead.
The principle will be: Use SUBSTRING three times on each date, to break it into teh day, month and year. Then use the CONVERT or CAST functuions to convert it to a usable date for comparison. If you do this in your SELECT, you may have to do it twice, once of the lower date range, and once for the upper.
It's not difficult, but it is tedious and a bit fiddly. I would suggest you try in in whatever application you are using for your database admin before you transfer it to an outside program and a SELECT statement there - you should get better and quicker diagnostics.

tulasiram3975 13-May-11 5:31am

Its not possible ...

OriginalGriff 13-May-11 5:35am

Yes, it is possible.
Try it, and see.

tulasiram3975 13-May-11 5:44am

Sir,
Can You Give A Sample Query For Above Table So That I May Proceed I tried Many Ways But Am Not Getting

OriginalGriff 13-May-11 5:54am

Answer Updated

tulasiram3975 13-May-11 7:04am

Sir Actually I Need In Between Dates I Think It Will Converts Date to yyyy-mm-dd This Format And Returns All Dates As yyyy-mm-dd. But How To Retrive In Between Dates

OriginalGriff 13-May-11 7:29am

Yes. I know. And when I said "give you a clue" what did you think I meant?
You have to do some of this for yourself.
The sql I gave you converts your text date into an SQL date variable.
If you were to use this in a WHERE clause, what do you think would happen?
What would happen if you used this twice in a WHERE clause?
For goodness sake, do I have to give it to you on a plate?

tulasiram3975 13-May-11 11:39am

No No...
ok i will do something thank you i got some idea

BobJanova 13-May-11 5:31am

This change will prevent a lot of nastiness like this in future. If it is your software, not some third party thing which you don't have access to, you SHOULD go through the small amount of pain now to fix it. And remember it, so next time you design a database, you don't make the same mistake!

tulasiram3975 13-May-11 5:47am

Next Time I Wont Do This Mistake But This Is Third Party Application Database Been Set In Client System Already Thats Why Am Not Changing Type.

saxenaabhi6 13-May-11 4:05am

Thats y i suggested you to do a view with a custom computed column of a proper date and query to that.

OriginalGriff 13-May-11 4:18am

Not exactly what he asked for: you can only do a view on data that has been acquired. If your DB has a lot of records and you are only interested in ten, then that's a heck of a waste!

Query to get data in between two dates in (type is varchar and formate is dd/mm/yyyy) mysql

5 solutions

Solution 1

Solution 4

Solution 2

Solution 3

Solution 5

Add your solution here

Preview 0

Query to get data in between two dates in (type is varchar and formate is dd/mm/yyyy) mysql

5 solutions

Solution 1

Solution 4

Solution 2

Solution 3

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us