Question Searching between 2 DateTime Values using SELECT statement

[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 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

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 9:00 AM and 11: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 9:00 AM and 11: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 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.

 

[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.

 

[cjard]

Please read the PQ link in my signature

Then write your SQLs properly:

'do this one time!
Dim cmd as New OleDbCommand("SELECT whatever FROM orders WHERE orderDate BETWEEN ? AND ?")
'this is dummy data!
cmd.Parameters.AddWithValue("from", DateTime.Now)
cmd.Parameters.AddWithValue("to", DateTime.Now)
'end of one-time setup

'heres how to use the sql many times
cmd.Parameters("from").Value = myFrom1DateTime
cmd.Parameters("to").Value = myTo1DateTime
myVal1 = cmd.ExecuteScalar()

cmd.Parameters("from").Value = myFrom2DateTime
cmd.Parameters("to").Value = myTo2DateTime
myVal2 = cmd.ExecuteScalar()

Set up once, reuse many times. Parameterize!


Yes there is no problem using aggregate functions with dates but remember:

Dates are stored as a DECIMAL number of the number of days since some event, like 01 January 1900.

If today at midnight is stored as 1234, then today at noon is stored as 1234.5

If you ask for all records between yesterday and today you will not get noon records today, because NUMERICALLY "yesterday" without a time defaults to midnight i.e. your query runs BETWEEN 1233 and 1234.

1234.5 is NOT between these range