Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET MySQL
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 22:34pm
Rate this: bad
good
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!
 
"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:
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!
  Permalink  
v3
Comments
tulasiram3975 at 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 at 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 at 13-May-11 5:31am
   
Its not possible ...
OriginalGriff at 13-May-11 5:35am
   
Yes, it is possible.
Try it, and see.
tulasiram3975 at 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 at 13-May-11 5:54am
   
Answer Updated
tulasiram3975 at 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 at 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 at 13-May-11 11:39am
   
No No...
ok i will do something thank you i got some idea
BobJanova at 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 at 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 at 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 at 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
good
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 Wink | ;)
  Permalink  
v5
Comments
tulasiram3975 at 13-May-11 5:53am
   
Thanks For Suggestion...
Rate this: bad
good
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
convert(datetime,'12/11/2008')
  Permalink  
Rate this: bad
good
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.
  Permalink  
v2
Rate this: bad
good
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');
  Permalink  

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

  Print Answers RSS
0 George Jonsson 215
1 Kornfeld Eliyahu Peter 169
2 OriginalGriff 120
3 PIEBALDconsult 110
4 BillWoodruff 85
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 28 Dec 2012
Copyright © CodeProject, 1999-2014
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