By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

Searching between 2 DateTime Values using SELECT stmt in Vb.Net

100+
P: 115
Im using VB.net 2003 application program.

I am trying to do a select statement whereby I'm searching between 2 datetime values that are being stored as datetime. records are stored inside Access.

For example, Im searching between 2 datetime
StartTime = 2/23/2009 10:00:00 AM
EndTime = 2/23/2009 12:30:00 AM
So I need to find all the records in between 10:00 AM and 12:30 AM on 2/23/2009.


i tried this code
Expand|Select|Wrap|Line Numbers
  1. strSQL = "select OrderID from Orders Where OrderDate >= ('" & StartTime & "') AND OrderDate <= ('" & EndTime & "') "
  2.  
but i got the error showing below
An unhandled exception of type 'System.Data.OleDb.OleDBException' occured in system.data.dll

Then i tried this code
Expand|Select|Wrap|Line Numbers
  1. strSQL = "select OrderID from Orders Where OrderDate >= DATEVALUE('" & StartTime & "') AND OrderDate <= DATEVALUE('" & EndTime & "') "
  2.  
But when I use DATEVALUE, it takes the date from the string and set time as midnight (00:00:00). So it returns no records between 10:00 AM and 12:30 AM on 2/23/2009, but I can see there are records.


Then i tried this code
Expand|Select|Wrap|Line Numbers
  1. strSQL = "select OrderID from Orders Where OrderDate >= TimeValue('" & StartTime & "') AND OrderDate <= TimeValue('" & EndTime & "') "
  2.  
And when I use TimeValue, it returns the time from the string and set date as jan 1st. so it returns no records between 10:00 AM and 12:30 AM on 2/23/2009, but I can see there are records.

Then i tried this code
Expand|Select|Wrap|Line Numbers
  1. strSQL = "select OrderID from Orders Where (OrderDate BETWEEN DATEVALUE('" & StartTime & "') AND DATEVALUE('" & EndTime & "')) "
  2.  
Expand|Select|Wrap|Line Numbers
  1. strSQL = "select OrderID from Orders Where (OrderDate BETWEEN ('" & StartTime & "') AND ('" & EndTime & "')) "
  2.  
but none of the codes above is returning records between 10:00 AM and 12:30 AM on 2/23/2009, but I can see there are records.

i searched and found all those examples. but that didn't work. Is there anyway i can search between 2 datetime values. i need to find all the records that lies between that time period (for example: between 10:00 AM and 12:30 AM on 2/23/2009).

If you have any idea how to do this, please let me know. if you can provide an example, then that will be great help for me.

Thanks in advance.
Feb 24 '09 #1
Share this Question
Share on Google+
1 Reply


100+
P: 115
when i tried these codes and it start working...
Expand|Select|Wrap|Line Numbers
  1.  strSQL = "select OrderDate from Orders Where OrderDate between (datevalue('" & StartTime & "') + timevalue('" & StartTime & "')) and (datevalue('" & EndTime & "') + timevalue('" & EndTime & "')) "
  2.  
Expand|Select|Wrap|Line Numbers
  1. strSQL = "select OrderDate from Orders Where OrderDate >= (datevalue('" & StartTime & "') + timevalue('" & StartTime & "')) and OrderDate <= (datevalue('" & EndTime & "') + timevalue('" & EndTime & "')) "
  2.  
But when i try
Expand|Select|Wrap|Line Numbers
  1.  strSQL = "select COUNT(*) from Orders Where OrderDate between (datevalue('" & StartTime & "') + timevalue('" & StartTime & "')) and (datevalue('" & EndTime & "') + timevalue('" & EndTime & "')) "
  2.  
  3. OR
  4.  
  5.  strSQL = "select SUM(gTotal) from Orders Where OrderDate between (datevalue('" & StartTime & "') + timevalue('" & StartTime & "')) and (datevalue('" & EndTime & "') + timevalue('" & EndTime & "')) "
  6.  
  7.  
Expand|Select|Wrap|Line Numbers
  1. strSQL = "select COUNT(*) from Orders Where OrderDate >= (datevalue('" & StartTime & "') + timevalue('" & StartTime & "')) and OrderDate <= (datevalue('" & EndTime & "') + timevalue('" & EndTime & "')) "
  2.  
  3. OR
  4.  
  5. strSQL = "select SUM(gTotal) from Orders Where OrderDate >= (datevalue('" & StartTime & "') + timevalue('" & StartTime & "')) and OrderDate <= (datevalue('" & EndTime & "') + timevalue('" & EndTime & "')) "
  6.  
Then its showing the below error
An unhandled exception of type 'System.Data.OleDb.OleDBException' occured in system.data.dll
but when i try this
Expand|Select|Wrap|Line Numbers
  1.  strSQL = "select COUNT(*) from Orders "
  2.  
Expand|Select|Wrap|Line Numbers
  1. strSQL = "select SUM(gTotal) from Orders "
  2.  
Then its returns the values.

Is it possible to use COUNT(*) or SUM(gTotal) while i'm checking DateTime Value? If you have any idea please help me.

Thanks in advance.
Feb 24 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.