Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > sql server > insights

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

Member
  
Join Date: Apr 2007
Posts: 99
#1   Feb 24 '09
I’m 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, I’m searching between 2 datetime
Quote:
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
Quote:
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.



Member
  
Join Date: Apr 2007
Posts: 99
#2   Feb 24 '09

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

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
Quote:
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.
Reply

« previous insight | next insight »

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,419 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved