Select data between two dates

HalRau · 09-18-2012, 04:57 PM

I am trying to select records from an Access database that has a date field formatted dd/mm/yyyy format and are between two dates defined in Dim parameters.
The code I have tried is this:

Code:

<%
Dim Today, Future
Today = (Date())
Future = (DateAdd("d",60,Today))
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\access_db\concerts.mdb;"
objConn.Open
Set objRs4=Server.CreateObject("ADODB.RecordSet")
 sql4="SELECT * FROM concert_data WHERE Cncrt_dateasp BETWEEN '" & Today & "' AND '" & Future & "' ORDER BY Cncrt_dateseq DESC"
 objRs4.Open sql4, Conn
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
objRs4_numRows = objRs4_numRows + Repeat1__numRows
%>

When I execute this script I get the following error:
"ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/performdates.asp, line 12"
Line 12 = objRs4.Open sql4, Conn

Your help is really appreciated.

Old Pedant · 09-18-2012, 11:24 PM

First of all, the error is dead obvious:

Code:

objConn.Open
objRs4.Open sql4, Conn

No place prior to that objRs4.Open line have you ever defined the variable Conn.

***********

But now we come to your next problem: Your query won't work.

You say your date is in the format dd/mm/yyyy.

Okay, consider these two dates, 60 days apart:

Code:

20/09/2012
19/11/2012

And now let's assume that the value in your database is

Code:

03/10/2012

There is NO POSSIBLE WAY that '03' can be between '20' and '19'.

When you store dates AS STRINGS, the ONLY way you can then compare them is if you store them as yyyy/mm/dd.

Because STRINGS are compared ONE CHARACTER AT A TIME. And the first mismatch trigger the < or > result. In this case, the '0' of '03' is less than both the '2' or '20' and the '1' of '19' and so is NOT between them.

There MAY be hope for you:

What is the DATA TYPE of the field Cncrt_dateasp in your table?

Use Access (the program, that is) to look at your table and examine that table deifinition and tell us the data type.

HalRau · 09-19-2012, 01:07 AM

Hi Old Pendant,
You helped me solve part of the problem.
The date format was/is the issue as you correctly observed.

Revised code:

Code:

<%
Dim Today, Future
'Today = ("2012-09-18")
'Future = ("2012-10-23")
Today = (Date())
Future = (DateAdd("d",30,Today))
response.write (Today) 
response.write (Future)

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Hosting\wayn4797\access_db\concerts.mdb;"
objConn.Open
Set objRs4=Server.CreateObject("ADODB.RecordSet")
 sql4="SELECT * FROM concert_data WHERE Cncrt_dateseq BETWEEN '" & Today & "' AND '" & Future & "' ORDER BY Cncrt_dateseq DESC"
 objRs4.Open sql4, objConn, 3, 3
' rs.Open strSQLQuery, conn, 3, 3
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
objRs4_numRows = objRs4_numRows + Repeat1__numRows
%>

If I remove the ' from in front of the Today and Future with the dates in yyyy-mm-dd format it works fine. That is the format of the Cncrt_dateseq data in the database.
Now I need to know how to get the (Date()) and (DateAdd("d",30,Today)) to be in the correct format.

Any ideas?

Thank you so much for your help.

Old Pedant · 09-19-2012, 03:37 AM

Quote:

If I remove the ' from in front of the Today and Future with the dates in yyyy-mm-dd format it works fine.

That makes no sense.

If you do that, they you will be doing something like

Code:

... WHERE Cncrt_dateseq BETWEEN 2012-09-19 AND 2012-11-18 ...

and the SQL engine will see that as *SUBTRACTION*!! That is, it will see it as
2012
minus 09 (==>>1993)
minus 19 (==>>1974)

At least with the '...' around those values I could see it working.

Are you *SURE* the values in the database look like '2012-10-03' and not 20121003 (that is, numbers)??

Take a screen shot of an Access display of the table data and show it.

HalRau · 09-19-2012, 12:21 PM

Thanks again for your help Old Pedant.
The database values for Cncrt_dateseq are as shown. They are a text field. There is also a field Cncrt_dateasp that is in a dd/mm/yyyy format. I tried using that but get extra events in the result, see it here, http://www.wayneoquin.com/performdates.asp

Here is screen shot of database table.

Old Pedant · 09-19-2012, 11:58 PM

Okay... It's too bad those are text fields, but luckily Access (JET) has an answer for that.

We can use *EITHER* the CNCRT_DATE or the CNCRT_DATESEQ field. Doesn't matter. Either will work. (But not the cncrt_dateasp field.)

Code:

SELECT * FROM concert_data 
WHERE CDATE(Cncrt_dateseq) BETWEEN DATE() AND DATE()+60
ORDER BY Cncrt_dateseq DESC

Try that! In Access, before you try using it in your ASP code.

Notice that you NO LONGER need to pass in Today and Future from the ASP code. Let Access do the date arithmetic for you!

If that works (and I'm 90% sure it will), then you can just convert it to ASP thus:

Code:

sql4 = "SELECT * FROM concert_data " _
     & " WHERE CDATE(Cncrt_dateseq) BETWEEN DATE() AND DATE()+60 " _
     & " ORDER BY Cncrt_dateseq DESC"

Old Pedant · 09-20-2012, 12:00 AM

If it's not obvious, CDATE() in Access works the same as it does in VBScript: IT takes the TEXT form of a date (or date + time) and converts it into the internal DATETIME format used by Access (and by VBScript). So in this case, it converts the text in the DB field and converts it to a DATETIME value.

HalRau · 09-20-2012, 12:28 PM

Hi again Old Pedant,
Your are a genius!
It works with Cncrt_Date. It does not work with Cncrt_Dateseq.
One last request.
I want to "highlight" the performance on the day of the performance would the code be like this:

Code:

<%if len(trim((objRS4.Fields.Item("Cncrt_Dateseq").Value))) = (DATE()) then%>
<h1><%=(objRS4.Fields.Item("Cncrt_Date").Value)%></h1>
<%else%>
<h3><%=(objRS4.Fields.Item("Cncrt_Date").Value)%></h3>
<%End if%>

I have tried both Cncrt_Date and Cncrt_Dateseq fields but neither work. I have also tried it with and without the brackets around the DATE.
Any suggestions?

Old Pedant · 09-20-2012, 08:16 PM

No. Not at all. WHY would you use LEN( )??? That just gets the length of the string. It would *NEVER* equal a DATE() value!

Code:

<%
If DATEVALUE( CDATE( objRS4("Cncrt_Dateseq") ) ) = DATE() Then
    Response.Write "<h1>" & objRS4("Cncrt_Date") & "</h1>"
Else
    Response.Write "<h3>" & objRS4("Cncrt_Date") & "</h3>"
End If
%>

p.s.: Although you *can* use code such as objRS4.Fields.Item("Cncrt_Date").Value as the incredibly idiotic DreamWeaver code does, there is no reason to do so!

Simply doing objRS4("Cncrt_Date") produces the IDENTICAL result *AND* executes roughly 3 times faster!

HalRau · 09-20-2012, 10:59 PM

Oh Dear! Asleep at the keyboard again

. That's what happens when you blindly copy working code from section to another without reviewing the code. ARRGH!!!
I tried your code above and it throws this error, Microsoft VBScript runtime error '800a000d'

Type mismatch: 'CDATE'

/performdates.asp, line 43
Line 43 reads:

Code:

<% If DATEVALUE( CDATE( objRS4("Cncrt_Dateseq") ) ) = DATE() Then %>

Any ideas?

Old Pedant · 09-20-2012, 11:27 PM

Well, it *SHOULD* work if all your values in that column match what you showed in that screen shot.

Example code:

Code:

<%
Response.Write CDATE("2012-05-30") & "<hr/>"
Response.Write CDATE("2011-02-10") & "<hr/>"
Response.Write CDATE("2009-09-10") & "<hr/>"
%>

But if any of your values there are not legal dates in the form YYYY-MM-DD then of course you could get that error.

So maybe do this:

Code:

<%
On Error Resume Next
    cncrtDate = objRS4("Cncrt_Dateseq")
    dt = DATEVALUE( CDATE( cncrtDate ) )
    If Err.Number > 0 Then 
        Response.Write "INVALID DATE:"
        dt = DATE()
    End If
On Error GoTo 0
If  dt = DATE() Then
    Response.Write "<h1>" & cncrtDate & "</h1>"
Else
    Response.Write "<h3>" & cncrtDate & "</h3>"
End If
%>

Now you will get the INVALID DATE message and it will show you which of the values from your DB has got a problem.

Old Pedant · 09-20-2012, 11:31 PM

AHHH!!! I *MISSED* this comment in one of your prior posts:

Quote:

It works with Cncrt_Date. It does not work with Cncrt_Dateseq.

That clearly indicates that one of the values in the CNCRT_DATESEQ indeed has a problem! If Access couldn't use that column, then neither could VBScript.

So just change to using that column in the IF test:

Code:

<%
If DATEVALUE( CDATE( objRS4("Cncrt_Date") ) ) = DATE() Then
    Response.Write "<h1>" & objRS4("Cncrt_Date") & "</h1>"
Else
    Response.Write "<h3>" & objRS4("Cncrt_Date") & "</h3>"
End If
%>

HalRau · 09-21-2012, 04:35 AM

Ha! that fixed the problem. It works fine now. Thank you for all your help. Again, you bailed me out.

JefferyJamison · 10-16-2012, 05:38 AM

Hi! Query to get records between two particular date like 07/08/2012 and 10/08/2012 then use below query:

SELECT * FROM #temp WHERE CONVERT(VARCHAR(10),CrDate,103) BETWEEN CONVERT(VARCHAR(10),'07/08/2012',103) AND CONVERT(VARCHAR(10),'10/08/2012',103)

OR

Syntax Query to get data between any two dates then you use below query :

SELECT Column1,Column2,Column3 FROM TABLENAME WHERE Column3 BETWEEN GetDate()-1 AND GetDate()

Old Pedant · 10-16-2012, 08:36 PM

WRONG ANSWER

Those queries are for SQL SERVER ONLY.

This thread was about JET OLEDB queries. That is, queries into an ACCESS database.

Neither the Access nor JET drivers have CONVERT() or GETDATE() functions.

Those queries aren't even very good if you are using SQL Server.

JUST FOR EXAMPLE:

Code:

    ... WHERE column3 BETWEEN GetDate()-1 AND GetDate()

GetDate() returns the current date *AND TIME*.

So let's say you make that query at 17:00:00 (5 PM) today.

Essentilly, you are asking for

Code:

 ... WHERE column3 BETWEEN (5 PM yesterday ) AND ( 5 PM today )

So, for example, you won't find records from 11 AM yesterday.

WORSE: If column3 is a DATE-ONLY column, then you wont' find ANY records from yesterday.

I think you might want to go revisit your SQL Server documentation to find better ways than what you are doing there.