Udvælg poster i database med fra og til bestemte datoer

SELECT * FROM dintabel WHERE datofelt BETWEEN #02-03-2003# AND #15-06-2003#

http://www.w3schools.com/sql/sql_between.asp

Hurtigt svaret. Jeg har du brug for lidt mere hjælp. Medsender derfor en søgemaskine, som jeg ikke kan få til at virke!! med datofelt.

<%@ Language=VBScript %>
<!-- METADATA TYPE="typelib" UUID="00000200-0000-0010-8000-00AA006D2EA4" NAME="ADO Type Library"-->

<%
Option Explicit
Response.Buffer = True        'Turn buffering on
Response.Expires = -1        'Page expires immediately

'Constants
Const MIN_PAGESIZE = 5        'Minimum pagesize
Const MAX_PAGESIZE = 20        'Maximum pagesize
Const DEF_PAGESIZE = 10        'Default pagesize

'Variables
Dim objCn                    'ADO DB connection object
Dim objRs                    'ADO DB recordset object
Dim blnWhere                'True/False for have WHERE in sql already
Dim intRecord                'Current record for paging recordset
Dim intPage                    'Requested page
Dim intPageSize                'Requested pagesize
Dim sql                        'Dynamic sql query string

'Create objects
Set objCn = Server.CreateObject("ADODB.Connection")
Set objRs = Server.CreateObject("ADODB.Recordset")

'Set/initialize variables
intRecord = 1
blnWhere = False

'-Get/set requested page
intPage = MakeLong(Request("page"))
If intPage < 1 Then intPage = 1

'-Get/set requested pagesize
If IsEmpty(Request("pagesize")) Then    'Set to default
    intPageSize = DEF_PAGESIZE
Else
    intPageSize = MakeLong(Request("pagesize"))
    'Make sure it fits our min/max requirements   
    If intPageSize < MIN_PAGESIZE Then
        intPageSize = MIN_PAGESIZE
    ElseIf intPageSize > MAX_PAGESIZE Then
        intPageSize = MAX_PAGESIZE
    End If
End If

'-Build dynamic sql
sql = "SELECT ID, Sku, Title, date FROM Products "

'--ID (exact search only)
If Not IsEmpty(Request("id")) Then
    If IsNumeric(Request("id")) Then
        'Add to query
        'First item so we don't have to test for WHERE
        blnWhere = True 'Set where to true
        sql = sql & "WHERE "
        sql = sql & "(ID = " & CStr(CLng(Request("id"))) & ") "
    End If
End If

'--Sku (partial and exact search)
If Not IsEmpty(Request("sku")) Then
    Dim strSku
    strSku = Trim(Request("sku"))
   
    If strSku <> "" Then
        'Test for WHERE
        If blnWhere Then sql = sql & "AND " Else sql = sql & "WHERE " : blnWhere = True
       
        If (Left(strSku, 1) = "*" And Len(strSku) > 1) Then    'Partial search
            sql = sql & "(Sku LIKE '%" & Replace(Mid(strSku, 2), "'", "''") & "') "   
        ElseIf (Right(strSku, 1) = "*" And Len(strSku) > 1) Then    'Partial search
            sql = sql & "(Sku LIKE '" & Replace(Mid(strSku, 1, Len(strSku)-1), "'", "''") & "%') "   
        Else    'Exact match
            sql = sql & "(Sku = '" & Replace(strSku, "'", "''") & "') "
        End If

    End If
End If

'--Title (parital search only)
If Not IsEmpty(Request("title")) Then
    Dim strTitle
    strTitle = Trim(Request("title"))
   
    If strTitle <> "" Then
        'Test for WHERE
        If blnWhere Then sql = sql & "AND " Else sql = sql & "WHERE " : blnWhere = True
        sql = sql & "(Title LIKE '%" & Replace(strTitle, "'", "''") & "%') "
    End If
End If

'--date (minimum)
If Not IsEmpty(Request("mindate")) Then
    If IsNumeric(Request("mindate")) Then
        Dim dblMindate
        dblMindate = CDbl(Request("mindate"))

        'Test for WHERE
        If blnWhere Then sql = sql & "AND " Else sql = sql & "WHERE " : blnWhere = True
        sql    = sql & "(date >= " & CStr(dblMindate) & ") "
    End If
End If
       
'--date (maximum)
If Not IsEmpty(Request("maxdate")) Then
    If IsNumeric(Request("maxdate")) Then
        Dim dblMaxdate
        dblMaxdate = CDbl(Request("maxdate"))

        'Test for WHERE
        If blnWhere Then sql = sql & "AND " Else sql = sql & "WHERE " : blnWhere = True
        sql    = sql & "(date <= " & CStr(dblMaxdate) & ") "
    End If
End If

'--Sort By Field
sql = sql & "ORDER BY "
Select Case Trim(LCase(Request("sortby")))
    Case "sku":        sql = sql & "Sku "
    Case "title":    sql = sql & "Title "
    Case "date":    sql = sql & "date "
    Case Else:        sql = sql & "ID "
End Select

'--Sort Order
Select Case Trim(LCase(Request("sortorder")))
    Case "desc":    sql = sql & "DESC"
    Case Else:        sql = sql & "ASC"
End Select
'--Dynamic sql finished

'Create and open connection object
With objCn
    .CursorLocation = adUseClient
    .ConnectionTimeout = 15
    .CommandTimeout = 30
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & Server.MapPath("../fpdb/eva.mdb") & ";"
    .Open
End With

'Create and open recordset object
With objRs
    .ActiveConnection = objCn
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .Source = sql
    .PageSize = intPageSize
    .Open
    Set .ActiveConnection = Nothing    'Disconnect the recordset
End With

'Creates a long value from a variant, invalid always set to zero
Function MakeLong(ByVal varValue)
    If IsNumeric(varValue) Then
        MakeLong = CLng(varValue)
    Else
        MakeLong = 0
    End If
End Function

'Returns a neatly made paging string, automatically configuring for request
'variables, regardless of in querystring or from form, adjust output to your needs.
Function Paging(ByVal intPage, ByVal intPageCount, ByVal intRecordCount)
    Dim strQueryString
    Dim strScript
    Dim intStart
    Dim intEnd
    Dim strRet
    Dim i

    If intPage > intPageCount Then
        intPage = intPageCount
    ElseIf intPage < 1 Then
        intPage = 1
    End If
   
    If intRecordCount = 0 Then
        strRet = "No Records Found"
    ElseIf intPageCount = 1 Then
        strRet = "End Of Hits"
    Else
        For i = 1 To Request.QueryString.Count
            If LCase(Request.QueryString.Key(i)) <> "page" Then
                strQueryString = strQueryString & "&"
                strQueryString = strQueryString & Server.URLEncode(Request.QueryString.Key(i)) & "="
                strQueryString = strQueryString & Server.URLEncode(Request.QueryString.Item(i))
            End If
        Next

        For i = 1 To Request.Form.Count
            If LCase(Request.Form.Key(i)) <> "page" Then
                strQueryString = strQueryString & "&"
                strQueryString = strQueryString & Server.URLEncode(Request.Form.Key(i)) & "="
                strQueryString = strQueryString & Server.URLEncode(Request.Form.Item(i))
            End If
        Next

        If Len(strQueryString) <> 0 Then
            strQueryString = "?" & Mid(strQueryString, 2) & "&"
        Else
            strQueryString = "?"
        End If

        strScript = Request.ServerVariables("SCRIPT_NAME") & strQueryString
   
        If intPage <= 10 Then
            intStart = 1
        Else
            If (intPage Mod 10) = 0 Then
                intStart = intPage - 9
            Else
                intStart = intPage - (intPage Mod 10) + 1
            End If
        End If

        intEnd = intStart + 9
        If intEnd > intPageCount Then intEnd = intPageCount
   
        strRet = "Page " & intPage & " of " & intPageCount & ": "
   
        If intPage <> 1 Then
            strRet = strRet & "<a href=""" & strScript
            strRet = strRet & "page=" & intPage - 1
            strRet = strRet & """>&lt;&lt;Prev</a> "
        End If
   
        For i = intStart To intEnd
            If i = intPage Then
                strRet = strRet & "<b>" & i & "</b> "
            Else
                strRet = strRet & "<a href=""" & strScript
                strRet = strRet & "page=" & i
                strRet = strRet & """>" & i & "</a>"
                If i <> intEnd Then strRet = strRet & " "
            End If
        Next
   
        If intPage <> intPageCount Then
            strRet = strRet & " <a href=""" & strScript
            strRet = strRet & "page=" & intPage + 1
            strRet = strRet & """>Next&gt;&gt;</a> "
        End If
    End If
   
    Paging = strRet
End Function
%>


<html>
<head>
<title>Database Sample by Shannon Harmon</title>
<style>
BODY
{
    FONT-WEIGHT: normal;
    FONT-SIZE: 9pt;
    COLOR: black;
    FONT-FAMILY: Arial, Helvetica, sans-serif;
    TEXT-DECORATION: none
}
TABLE
{
    FONT-WEIGHT: normal;
    FONT-SIZE: 9pt;
    COLOR: black;
    FONT-FAMILY: Arial, Helvetica, sans-serif;
    TEXT-DECORATION: none
}
A
{
    FONT-WEIGHT: normal;
    FONT-SIZE: 9pt;
    COLOR: blue;
    FONT-FAMILY: Arial, Helvetica, sans-serif;
    TEXT-DECORATION: underline
}
HR
{
    COLOR: #0072bc
}
</style>
</head>

<body onload="java script: document.frmSearch.id.select();">

<table border="0" width="100%" cellpadding="2" cellspacing="2">
    <tr><td align=center><h3>Database Sample by Shannon Harmon</h3></td></tr>
    <tr><td align=center><a href="mailto:shannonh@theharmonfamily.com">shannonh@theharmonfamily.com</a></td></tr>
    <tr><td><hr></td></tr>
   
    <!--Search Form-->
    <form name="frmSearch" method="post" action="<%=Request.ServerVariables("SCRIPT_NAME")%>">
    <input type="hidden" name="page" value="1">
    <tr>
        <td align=center>
            <table border="0" width="100%" cellpadding="2" cellspacing="2">
                <tr>
                    <td colspan="2"><b><font size="3"><font color="#336699">Product Search Options</font></b></td>
                </tr>
               
                <tr>
                    <td nowrap><b>Product <u>I</u>D:</b></td>
                    <td width="100%"><input accesskey="i" type="text" name="id" size="10" value="<%=Server.HTMLEncode(Request("id"))%>">&nbsp;(exact match)</td>
                </tr>
               
                <tr>
                    <td nowrap><b>Product <u>S</u>ku:</b></td>
                    <td width="100%"><input accesskey="s" type="text" name="sku" size="20" value="<%=Server.HTMLEncode(Request("sku"))%>"><br>(ie: *sx will return 10-sx, 20-sx; 10* will return 10-sx, 10-bx; 10-sx will return 10-sx only!)</td>
                </tr>
               
                <tr>
                    <td nowrap><b><u>T</u>itle:</b></td>
                    <td width="100%"><input accesskey="t" type="text" name="title" size="20" value="<%=Server.HTMLEncode(Request("title"))%>">&nbsp;(partial match)</td>
                </tr>
               
                <tr>
                    <td nowrap><b>Mi<u>n</u>imum date:</b></td>
                    <td width="100%"><input accesskey="n" type="text" name="mindate" size="10" value="<%=Server.HTMLEncode(Request("mindate"))%>"></td>
                </tr>                   

                <tr>
                    <td nowrap><b>Ma<u>x</u>imum date:</b></td>
                    <td width="100%"><input accesskey="x" type="text" name="maxdate" size="10" value="<%=Server.HTMLEncode(Request("maxdate"))%>"></td>
                </tr>
               
                <tr>
                    <td nowrap><b>Sort <u>B</u>y:</b></td>
                    <td width="100%">
                        <select accesskey="b" name="sortby">
                        <option value="id">Product ID</option>
                        <option value="sku"<%If Trim(LCase(Request("sortby"))) = "sku" Then Response.Write " selected"%>>Product Sku</option>
                        <option value="title"<%If Trim(LCase(Request("sortby"))) = "title" Then Response.Write " selected"%>>Title</option>
                        <option value="date"<%If Trim(LCase(Request("sortby"))) = "date" Then Response.Write " selected"%>>date</option>
                        </select>
                        &nbsp;
                        <select name="sortorder">
                        <option value="asc">Ascending</option>
                        <option value="desc"<%If Trim(LCase(Request("sortorder"))) = "desc" Then Response.Write " selected"%>>Descending</option>
                        </select>
                    </td>
                </tr>

                <tr>
                    <td nowrap><b><u>R</u>ecords Per Page:</b></td>
                    <td width="100%"><input accesskey="r" type="text" name="pagesize" size="10" value="<%=intPageSize%>"></td>
                </tr>

                <tr>
                    <td colspan="2">
                    <input type="button" name="btnRestart" value="Start Over" onclick="java script: window.location='<%=Request.ServerVariables("SCRIPT_NAME")%>'">
                    &nbsp;&nbsp;
                    <input type="submit" name="btnSubmit" value="Go!">
                    </td>
                </tr>
            </table>
        </td>
    </tr>
    </form>
   
    <tr><td><hr></td></tr>
   
    <%If objRs.EOF Then%>
    <!--No Records Found-->
    <tr><td align="center"><b><font size="4">No products found!</font></b></td></tr>

    <%Else%>
    <!--Records Found-->
    <tr>
        <td>
            <table border="0" width="100%" cellpadding="3" cellspacing="3">
                <tr><td colspan=4><b>Products Found: <%=objRs.RecordCount%></td></tr>
                <tr><td align="center" colspan="4"><%=Paging(intPage, objRs.PageCount, objRs.RecordCount)%></td></tr>

                <tr bgcolor="#efefef">
                    <td nowrap><b>Product ID</b></td>
                    <td nowrap><b>Sku</b></td>
                    <td nowrap width="100%"><b>Title</b></td>
                    <td nowrap align="right"><b>date</b></td>
                </tr>
               
                <%
                If objRs.PageCount < intPage Then intPage = objRs.PageCount
                objRs.AbsolutePage = intPage
               
                Dim strRowColor
                strRowColor = "#ffffff"
               
                Do While Not objRs.EOF And intRecord <= intPageSize
                    %>
               
                <tr bgcolor="<%=strRowColor%>">
                    <td nowrap><%=objRs("ID").Value%></td>
                    <td nowrap><%=objRs("Sku").Value%></td>
                    <td><%=objRs("Title").Value%></td>
                    <td nowrap align="right"><%=objRs("date").Value%></td>
                </tr>
                    <%
                    If strRowColor = "#ffffff" Then strRowColor = "#90ee90" Else strRowColor = "#ffffff"
                    intRecord = intRecord + 1
                    objRs.MoveNext
                Loop
                %>
                <tr><td colspan="4"><hr></td></tr>
                <tr><td align="center" colspan="4"><%=Paging(intPage, objRs.PageCount, objRs.RecordCount)%></td></tr>
            </table>
        </td>
    </tr>
   
    <%End If%>
</table>

</body>
</html>

<%
'Object cleanup
If IsObject(objRs) Then
    If Not objRs Is Nothing Then
        If objRs.State = adStateOpen Then objRs.Close
        Set objRs = Nothing
    End If
End If

If IsObject(objCn) Then
    If Not objCn Is Nothing Then
        If objCn.State = adStateOpen Then objCn.Close
        Set objCn = Nothing
    End If
End If
%>

Mange tak - og hvad er problemet?

Kan ikke få siden til at virke korrekt ved input af to datoer i formatet. dd-mm-åååå.

Prøv at rette til følgende. Du må heller ikke bruge dansk datoformat i en SQL-sætning. Brug enten mm-dd-yyyy eller endnu bedre yyyy-mm-dd.


'--date (minimum)
If Not IsEmpty(Request("mindate")) Then
    If IsNumeric(Request("mindate")) Then
        Dim dblMindate
        dblMindate = CDbl(Request("mindate"))

        'Test for WHERE
        If blnWhere Then sql = sql & "AND " Else sql = sql & "WHERE " : blnWhere = True
        sql    = sql & "(date >= '" & CStr(dblMindate) & "') "
    End If
End If
       
'--date (maximum)
If Not IsEmpty(Request("maxdate")) Then
    If IsNumeric(Request("maxdate")) Then
        Dim dblMaxdate
        dblMaxdate = CDbl(Request("maxdate"))

        'Test for WHERE
        If blnWhere Then sql = sql & "AND " Else sql = sql & "WHERE " : blnWhere = True
        sql    = sql & "(date <= '" & CStr(dblMaxdate) & "') "
    End If
End If

Virker desværre ikke. Kan jeg få et eksempel på en søgemaskine, der kan håndtere datoer.

prøv at erstatte tegnet ' med tegnet # før og efter dine datostrenge:
f.eks.
sql    = sql & "(date >= #" & CStr(dblMindate) & "#) "

i stedet for

sql    = sql & "(date >= '" & CStr(dblMindate) & "') "

her ud over har netro nok ret i at dine datostrenge skal være i formaterne "enten mm-dd-yyyy eller endnu bedre yyyy-mm-dd." (26/08-2003 16:54:52)

lukketid?