[ehymel]

A predecessor designed a database that stores a text field that the end user fills with a date, like "Monday, August 13, 2007". Now of course the end user wants to sort the output by date (previously events were entered in date order so that retrieving alread had things in order). The web page that hits the database is written in VB Script under ASP. I'm looking for a function that can sort these dates either within the SQL query or afterwards in VB Script. I could kludge something together, but I'd prefer something elegant.

My current SQL query:

strSQL = "SELECT Event, EventDate FROM AcademicCalendar ORDER BY EventID;"

where EventDate is a text field (not a true SQL date field).

Thanks in advance!

[ehymel]

I solved this one on my own. Probably far from the most elegant, but this is what I did:

The overall strategy is to pull the records from the database into a new array. That array consists of the fields I'm interested in plus an additional column that is a sortable date. That new date column has be be constructed from the text field in my database that contains a date as described in my original post. I use VBScript's regular expressions to extract the date portion of the text field then convert it to a useable date using CDate. Then the whole array is sorted on the new CDate field before showing the results.

First, I query the database and retrieve the recordset into oRS. The recordset has a forward-only cursor so I use the method described here to count the records to set up my array size. Note the need to use the .MoveFirst method to move back to the beginning of the record set.

The key to sorting is the function at the end (QuickSort). That function was "borrowed" from here.

<%
Dim regEx, strMatchDate, arrResults()
arrTemp = oRS.GetRows()
intRecordCount = UBound(arrTemp,2)
oRS.MoveFirst
ReDim arrResults(intRecordCount,4)   ' I have 4 field to display
Set regEx = New RegExp
With regEx
	.Global = False
	.IgnoreCase = True
	.Pattern = "[a-z]+\s[0-9]+,\s[0-9]{4}"	' matches on pattern Month Date, Year
End With
intCount = 0
Do While Not oRS.EOF
	strDate = oRS("Dates")
	arrResults(intCount,0) = strDate
	arrResults(intCount,1) = oRS("Item")
	' arrResults(intCount,2) will be the field I sort on... see below.
	arrResults(intCount,3) = oRS("TermID")
	arrResults(intCount,4) = oRS("Term")

	Set strMatchDate = regEx.Execute(strDate)
	For Each strMatch in strMatchDate
		If IsDate(strMatch.Value) Then 
			arrResults(intCount,2) = CDate(strMatch.Value)
		End If
	Next
	intCount = intCount + 1
	oRS.MoveNext
Loop
Set regEx = Nothing
Set strMatchDate = Nothing
Call QuickSort(arrResults,0,intCount-1,2)
%>

From here it's just a matter of iterating through the sorted array and formattting the output.

Hope this helps someone.