Problems with SQL Selection with UK date formats

I've got a database of walking trips that are arranged for dates in the future. The database is on a machine with UK locale, and the dates are being entered into the database in UK format. My problem is that when I use the following code to select walks with a date greater than or equal to todays date it is using american format for the selection and therefore not picking up the correct walks from the database.

Any clues?

your help is much appreciated.

Thanks

Code:

---

<%
'The code below sets the locale of the system to UK format.
'This is to make sure the date will always be displayed in uk format on all pages
        Sub SetUKLocale()
        Session.lcid = 2057
        End Sub
                       
        Call SetUKLocale()
                       
'The following code declares and sets the variable properties relating to the paging of the recordset.
        Dim iStart, iOffset
                       
        iStart = Request("Start")
        iOffset = Request("Offset")
                       
        if Not IsNumeric(iStart) or Len(iStart) = 0 then
        iStart = 0 'sets the starting record that is displayed on the page
        else
        iStart = CInt(iStart)
        end if
                       
        if Not IsNumeric(iOffset) or Len(iOffset) = 0 then
        iOffset = 10 'sets the number of records to be displayed per page
        else
        iOffset = Cint(iOffset)
        end if
                       
        Response.Write "<p class=""rspaging"">Viewing " & iOffset & " records starting at record " & iStart & "</p>"
                       
'The following code sets up the connection to the database and creates a recordset of data.               
        Dim objRS, objConn, strConn, strSQL, strDate 'Declare variables.
                       
        strDate = FormatDateTime(Date,2) 'Set the date to short date format
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="& Server.MapPath("Walkers.mdb")
        strSQL = "SELECT WalkNo, Venue, WalkDate, Leader FROM Walk WHERE WalkDate >= #" & strDate & "# ORDER BY WalkNo DESC"
               
        Set objConn = Server.CreateObject("ADODB.Connection")
        objConn.Open strConn
        Set objRS = Server.CreateObject("ADODB.Recordset")
        objRS.CursorType = 2
        objRS.LockType = 3
        objRS.Open strSQL, objConn
                       
'The following code copies the recordset to an array
        Dim aWalkValues 'Declare variables.
        aWalkValues = objRS.GetRows()
                       
'The following constants are used for identifying the fields of the array within the table               
        const MyWalkNo = 0
        const MyVenue = 1
        const MyWalkDate = 2
        const MyLeader = 3
                       
'Here is where the recordset and database connection are closed
        objRS.Close
        Set objRS = Nothing
        objConn.Close
        Set objConn = Nothing
               
'The following code sets up the table and loops through the array displaying the results.
        Dim iRowLoop, iColLoop, showNull, showBlank, numcols, numrows, rowcounter, colcounter, thisfield, iStop 'Declare variables.
                                               
        Response.Write("<table width=""670px"" cellspacing=""0"" cellpadding=""0""><tr>") & vbcrlf
        Response.Write("<th>Walk No.</th>") & vbcrlf
        Response.Write("<th>Venue</th>") & vbcrlf
        Response.Write("<th>Walk Date</th>") & vbcrlf
        Response.Write("<th>Leader</th>") & vbcrlf
        Response.Write("</tr>") & vbcrlf
                       
        If isArray (aWalkValues) then
        numcols=ubound(aWalkValues,1)
        numrows=ubound(aWalkValues,2)
                       
        If numrows > (iOffset + iStart) Then
        iStop = iOffset + iStart - 1
        Else iStop = numrows
        End If
                       
        For rowcounter = iStart TO iStop
        response.write "<tr>" & vbcrlf
        response.write "<td><a href=""walk_detail.asp?WalkNo=" & aWalkValues(MyWalkNo, rowcounter) & """>" & aWalkValues(MyWalkNo, rowcounter) & "</a></td>" & vbcrlf
        response.write "<td>" & aWalkValues(MyVenue, rowcounter) & "</td>" & vbcrlf
        response.write "<td>" & aWalkValues(MyWalkDate, rowcounter) & "</td>" & vbcrlf
        response.write "<td>" & aWalkValues(MyLeader, rowcounter) & "</td>" & vbcrlf
        response.write "</tr>" & vbcrlf
        Next
        response.write "</table>"
        End If
                       
'Here is where the links that page through the recordset are displayed.
        Response.write "<p class=""rspaging"">"
        if iStart > 0 then
'Show Prev link
        Response.Write "<a href=""walklist.asp?Start=" & iStart-iOffset & _
        "&amp;Offset=" & iOffset & """>Previous" & iOffset & "</a>&nbsp;&nbsp;"
        end if
                       
        if iStop < numrows then
'Show Next link
        Response.Write "<a href=""walklist.asp?Start=" & iStart+iOffset & _
        "&amp;Offset=" & iOffset & """>Next " & iOffset & "</a>"
        end if
        Response.Write "</p>"
        Response.Write "<p class=""rspaging"">Todays Date is: " & strDate & "</p>"
%>

---

Fixed! :thumb: :D

I converted the date to yyyy/mm/dd format as suggested in http://www.aspfaq.com/show.asp?id=2040

I changed

Code:

---

strDate = FormatDateTim(Date,2)

---

to

Code:

---

strDate = year(date) &_
                left("/00",2-len(month(date))) & month(date) &_
                left("/00",2-len(day(date))) & day(date)

---

and it worked. Hope this helps someone else...