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]

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

[Frinavale]

Is OrderDate a varchar or a Date?

[remya1000]

OrderDate is Date/Time field.

[NeoPa]

Check out Literal DateTimes and Their Delimiters (#) and let me know afterwards if you are still experiencing any problems.

[Stewart Ross]

Hi. The main problem with your first try at this one is that you are passing date/time literals as if they are text within single quotes, and you do the same later when using DateValue and TimeValue, which take date/time arguments, not text strings.

The convention for the JET database engine when referring to date literals is to use # (the pound or hash character) as a delimiter. Substituting in your original SQL string:

strSQL = "select OrderID from Orders Where OrderDate >= #" & StartTime & "# AND OrderDate <= #" & EndTime & "#"

You can use the Between operator to shorten this a little:

strSQL = "select OrderID from Orders Where OrderDate Between #" & StartTime & "# AND #" & EndTime & "#"

Within VB itself if you need to use DateValue or TimeValue in code you would not use delimiters if the arguments are already of date/time type. However, you are building a SQL string that is passed outside of the VB environment and interpreted by the database engine. In this case you are not actually passing the variable to the function as a date/time type; you are passing the value of that variable as a literal. For that reason you'd include the # delimiters in your SQL string:

strSQL = "select OrderID from Orders Where OrderDate Between DATEVALUE(#" & StartTime & "#) + TimeValue(#" & StartTime & "#) AND DATEVALUE(#" & EndTime & "#) + TimeValue (#" & EndTime & "#)"

However, this last statement is just a much more complicated way of doing the first one shown above.

In summary, you need the # delimiters if you are passing date literals to the operators or function concerned. When you build SQL strings you cannot reference VB variables outside of the VB environment, so you have to pass their literal values enclosed within delimiters as part of the SQL string.

For more on date/time values in ANSI SQL and Access see our insight article date-time literals and their delimiters

-Stewart

ps take note of the format for dates to which SQL statements should conform, as listed in the insight article. I note that you have already specified that your date component is in m/d/y format, which should be compliant with the ANSI standard.