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

remya1000 · February 24th, 2009, 11:32 AM

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 9:00:00 AM”
EndTime = “2/23/2009 11:30:00 AM”

So I need to find all the records in between 9:00 AM and 11:30 AM on 2/23/2009.

i tried this code

Code:

strSQL = "select OrderID from Orders Where OrderDate >= ('" & StartTime & "') AND OrderDate <= ('" & EndTime & "') "

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

Code:

strSQL = "select OrderID from Orders Where OrderDate >= DATEVALUE('" & StartTime & "') AND OrderDate <= DATEVALUE('" & EndTime & "') "

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 9:00 AM and 11:30 AM on 2/23/2009, but I can see there are records.

Then i tried this code

Code:

strSQL = "select OrderID from Orders Where OrderDate >= TimeValue('" & StartTime & "') AND OrderDate <= TimeValue('" & EndTime & "') "

And when I use TimeValue, it returns the time from the string and set date as jan 1st. so it returns no records between 9:00 AM and 11:30 AM on 2/23/2009, but I can see there are records.
Then i tried this code

Code:

strSQL = "select OrderID from Orders Where (OrderDate BETWEEN DATEVALUE('" & StartTime & "') AND DATEVALUE('" & EndTime & "')) "

Code:

strSQL = "select OrderID from Orders Where (OrderDate BETWEEN ('" & StartTime & "') AND ('" & EndTime & "')) "

but none of the codes above is returning records between 9:00 AM and 11: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 9:00 AM and 11: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.

philip_cole · February 24th, 2009, 01:42 PM

Hi Remya,

The main problem you've got is that your criteria is wrong: 12.30 AM (half past midnight) comes before 10.00AM, so you will never get any results. You need to use 12.30PM (half past midday).

Also dates within access are different to some other systems in that you use # to work with them. So your query should become something like

Code:

SELECT OrderID FROM Orders WHERE OrderDate BETWEEN #02/23/2009 10:00:00 AM# AND #02/23/2009 12:30:00 PM#

Hope this helps
Phil

remya1000 · February 24th, 2009, 03:03 PM

Philip_Cole, thanks for the reply.

Actually inside my program i used EndTime - 2/23/2009 12:30:00 PM. but while typing in this forum i made a mistake and that changed to 2/23/2009 12:30:00 AM.

Even when i try
StartTime = 2/23/2009 9:00:00 AM
EndTime = 2/23/2009 10:00:00 AM
using all the codes above, it didn't worked.

As you said i tried this code too,

Code:

StartTime = 2/23/2009 9:00:00 AM
EndTime = 2/23/2009 10:00:00 AM
 
strSQL = "select OrderDate from Orders Where OrderDate BETWEEN # StartTime # AND # EndTime # "

Code:

StartTime = 2/23/2009 9:00:00 AM
EndTime = 2/23/2009 10:00:00 AM
 
strSQL = "select OrderDate from Orders Where OrderDate BETWEEN ' # StartTime # ' AND ' # EndTime # ' "

but i got the error showing below

Quote:

An unhandled exception of type 'System.Data.OleDb.OleDBException' occured in system.data.dll

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 9:00 AM and 11:30 AM on 2/23/2009).

If you have any idea how to do this, please help me.

Thanks in advance

remya1000 · February 24th, 2009, 03:49 PM

when i tried these codes and it start working...

Code:

 strSQL = "select OrderDate from Orders Where OrderDate between (datevalue('" & StartTime & "') + timevalue('" & StartTime & "')) and (datevalue('" & EndTime & "') + timevalue('" & EndTime & "')) "

Code:

strSQL = "select OrderDate from Orders Where OrderDate >= (datevalue('" & StartTime & "') + timevalue('" & StartTime & "')) and OrderDate <= (datevalue('" & EndTime & "') + timevalue('" & EndTime & "')) "

But when i try

Code:

 strSQL = "select COUNT(*) from Orders Where OrderDate between (datevalue('" & StartTime & "') + timevalue('" & StartTime & "')) and (datevalue('" & EndTime & "') + timevalue('" & EndTime & "')) "
OR
 strSQL = "select SUM(gTotal) from Orders Where OrderDate between (datevalue('" & StartTime & "') + timevalue('" & StartTime & "')) and (datevalue('" & EndTime & "') + timevalue('" & EndTime & "')) "

Code:

strSQL = "select COUNT(*) from Orders Where OrderDate >= (datevalue('" & StartTime & "') + timevalue('" & StartTime & "')) and OrderDate <= (datevalue('" & EndTime & "') + timevalue('" & EndTime & "')) "
OR
strSQL = "select SUM(gTotal) from Orders Where OrderDate >= (datevalue('" & StartTime & "') + timevalue('" & StartTime & "')) and OrderDate <= (datevalue('" & EndTime & "') + timevalue('" & EndTime & "')) "

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

Code:

 strSQL = "select COUNT(*) from Orders "

Code:

strSQL = "select SUM(gTotal) from Orders "

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.