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

[remya1000]

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

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

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


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

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


Then i tried this 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 10:00 AM and 12:30 AM on 2/23/2009, but I can see there are records.

Then i tried this code

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


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


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.

[remya1000]

i tried these codes and it start working...


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


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


But when i try

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 & "')) "



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

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


but when i try this

strSQL = "select COUNT(*) from Orders "


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.

[chazzy]

the only thing i can think of is to first try plugging in hard coded values into your query tool (not sure what you're using, since you didn't specify a dbms) and see what happens. nothing is standing out to me.

StartTime = “2/23/2009 10:00:00 AM”
EndTime = “2/23/2009 12:30:00 AM”


select COUNT(*) from Orders Where OrderDate between (datevalue('2/23/2009 10:00:00 AM') + timevalue('2/23/2009 10:00:00 AM')) and (datevalue('2/23/2009 12:30:00 AM') + timevalue('2/23/2009 12:30:00 AM')