Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 980
  • Last Modified:

How to make query to get data between specific date duration ?

Hi,
iv table which have a date column, in addition to a set of other columns, all what i need a simple select query to get * from myTable where dateColumn  value between (Start value) and (End value).
note, i'm using SQl server 2000

thanks,
0
palserv
Asked:
palserv
  • 4
  • 4
1 Solution
 
vsethCommented:
select * from myTable where dateColumn between StartValue and EndValue
0
 
palservAuthor Commented:
thanks for your answer,
but i need to know where the error in this code
Select * FROM myTable  WHERE Convert(DateTime, Date_of_Documentation,103)>= Convert(DateTime,dateTimePicker1.Value,103) and Convert(datetime,Date_of_Documentation,103) <= Convert(DateTime,dateTimePicker2.value,103)

i'm using visual studio 2005 and c# in development.
0
 
Mark WillsTopic AdvisorCommented:
Well, it kind of looks OK,

103 is dd/mm/yyyy, so, the convert is assuming that the datavalues can be readily expressed in that format. No worries for a dattime construct, but for a string / char / varchar (etc) then the content of that variable must must the format that the convert is told to use...

so, do not need to convert Date_Of_Documentation if already a legitimate datetime column.

I would be looking at :

Select * FROM myTable  WHERE Date_of_Documentation btween Convert(DateTime,dateTimePicker1.Value,103) and Convert(DateTime,dateTimePicker2.value,103)

Now, if there is also a time component, then we can / will need to start manipluating a bit more.

What is the error you are getting ? Is the date_of_documentation a legit datetime ? what is the content of the variables datetimepicker1 and 2 ?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
palservAuthor Commented:
Hi,
i'v tried your idea, but i still got this errer message
"The SELECT statment include a reserved word  or an argument name that is missplesed or missing or the punctuation is incorrect"

this is my statment :
query += " FROM Damaged_factories WHERE Date_of_Documentation BETWEEN Convert(DateTime,dateTimePicker1.Value,103)AND Convert(DateTime,dateTimePicker1.Value,103) and Convert(datetime,dateTimePicker2.Value,103)";

where Date_of_Documentation is a Date/Time data type
0
 
Mark WillsTopic AdvisorCommented:
one too many datetimepicker1:

instead of:

query += " FROM Damaged_factories WHERE Date_of_Documentation BETWEEN Convert(DateTime,dateTimePicker1.Value,103)AND Convert(DateTime,dateTimePicker1.Value,103) and Convert(datetime,dateTimePicker2.Value,103)";

use :

query += " FROM Damaged_factories WHERE Date_of_Documentation BETWEEN Convert(DateTime,dateTimePicker1.Value,103) and Convert(datetime,dateTimePicker2.Value,103)";

still need to handle the time component properly, but better off just changing one thing at a time...
0
 
palservAuthor Commented:
error msg : 'System.Convert' is a 'type' but is used like a 'variable'  :(

0
 
Mark WillsTopic AdvisorCommented:
D'oh, You will have to put the contents of the datetimepicker1.value into the string... kind of like...

" FROM Damaged_factories WHERE Date_of_Documentation BETWEEN Convert(DateTime,'"+dateTimePicker1.Value+"',103) and Convert(datetime,'"+dateTimePicker2.Value+"',103)";

will need to possibly convert the value so it can be concatenated...

the result should be the equivelant of:

" FROM Damaged_factories WHERE Date_of_Documentation BETWEEN Convert(DateTime,'01/01/2008',103) and Convert(datetime,'02/02/2008',103)";
0
 
palservAuthor Commented:
well i'v get this result " FROM Damaged_factories WHERE Date_of_Documentation BETWEEN Convert(DateTime,'01/01/2008',103) and Convert(datetime,'02/02/2008',103)"; completely
but this result cause an OleDbException in this message "Undefined function ''Convert in this Expression " 

i'm so confusing, all my problem is some of quotes which i didnt know there right place
0
 
Mark WillsTopic AdvisorCommented:
You are using SQL 2000 right ?

open up query analyser and simply paste :

SELECT * FROM Damaged_factories WHERE Date_of_Documentation BETWEEN Convert(DateTime,'01/01/2008',103) and Convert(datetime,'02/02/2008',103)

and see what happens ?
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now