Solved

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

Posted on 2008-06-15
9
972 Views
Last Modified: 2010-10-26
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
Comment
Question by:palserv
  • 4
  • 4
9 Comments
 
LVL 4

Expert Comment

by:vseth
ID: 21788016
select * from myTable where dateColumn between StartValue and EndValue
0
 

Author Comment

by:palserv
ID: 21788477
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21788598
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:palserv
ID: 21791271
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21791528
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
 

Author Comment

by:palserv
ID: 21792104
error msg : 'System.Convert' is a 'type' but is used like a 'variable'  :(

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21794256
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
 

Author Comment

by:palserv
ID: 21800940
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 21802222
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question