Solved

select statement

Posted on 2008-10-22
7
186 Views
Last Modified: 2012-05-05
I am using MS-SQL 2000. My column Date_time where it stamps date and time,   10/20/2008 12:49:47 PM.
I have a calendar where user can select a date. Sample 10/20/2008.  My select statement I have this  : select *from mytable where date_time between convert(datetime,  '10/20/2008') and convert(datetime, '10/22/2008' )  < this does not work.  I need help.
0
Comment
Question by:VBdotnet2005
[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
7 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22781354
The above statement should work as the converted datetime should be:
2008-10-20 00:00:00

Which will then make your date_time of 2008-10-20 12:49:47 fall within the range.

Please provide your exact code using from the ASP.NET page to construct the query.
0
 
LVL 2

Accepted Solution

by:
BobTheViolent earned 500 total points
ID: 22781353
I think it is just because of a missing space after the *.  It worked for me when I changed that.

Try
select * from mytable where date_time between convert(datetime,  '10/20/2008') and convert(datetime, '10/22/2008' )
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22781367
I agree.  Since I don't have your table data, I didn't use your query above and worked for me perfectly.  Therefore, if that is not a type-o, then please post your exact code as suggested since the SQL syntax is valid.
0
Independent Software Vendors: 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!

 
LVL 5

Expert Comment

by:eridanix
ID: 22784148
Hi,

I mean, that you have changed days and months in you date strings.
Try this:


select * 
from mytable 
where date_time between convert(datetime,  '20/10/2008') and convert(datetime, '22/10/2008' )

Open in new window

0
 

Author Comment

by:VBdotnet2005
ID: 22788747
One small problem I have is. When I do this
select * from mytable where date_time between convert(datetime,  '10/22/2008') and convert(datetime, '10/23/2008' )
I received the result on 10/22 only, nothing on the 23rd.

But if I do this

select * from mytable where date_time between convert(datetime,  '10/22/2008') and convert(datetime, '10/24/2008' )

I received the result from 10/22 to 10/23. Strange. Any ideas?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22788887
See my first comment.  You are creating date at midnight, so you will only get data between 10/22 12:00:00 AM and 10/23 12:00:00 AM which is pretty much just 10/22.
0
 

Author Comment

by:VBdotnet2005
ID: 22788953
thank you
0

Featured Post

Industry Leaders: 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

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

730 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