[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2008-06-15
9
Medium Priority
?
977 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

649 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