Click here to Skip to main content
13,139,834 members (41,216 online)
Rate this:
Please Sign up or sign in to vote.
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
Updated 12-Sep-17 22:56pm
Rate this: bad
Please Sign up or sign in to vote.

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!

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:
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!
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
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!
Rate this: bad
Please Sign up or sign in to vote.

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 ;)
tulasiram3975 13-May-11 5:53am
Thanks For Suggestion...
Rate this: bad
Please Sign up or sign in to vote.

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
Rate this: bad
Please Sign up or sign in to vote.

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.
Rate this: bad
Please Sign up or sign in to vote.

Solution 5

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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web04 | 2.8.170915.1 | Last Updated 13 Sep 2017
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100