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,
palservAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic 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
 
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.