Problems with SQL Selection with UK date formats

[andytate]

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

<%
'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>"
%>

[andytate]

Fixed!

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

I changed

strDate = FormatDateTim(Date,2)

to

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