converting a string to date

[dotolee]

i have records that i need to select for a specific user by date. The problem is that the date field is actually just a text field. here's my code so far:
set objCnn = Server.CreateObject("ADODB.Connection")
set objRS = Server.CreateObject("ADODB.Recordset")
set last5RS = Server.CreateObject("ADODB.Recordset")

objCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & logfilepath & "Extended Properties=""Text;"""
objRS.CursorLocation=adUseClient

objRS.Open "SELECT DISTINCT TOP 5 SearchText, SearchType, SearchDateTime FROM " & logfileName & " WHERE USERNAME='" & Session("username") & "' AND SEARCHSTATUS='SUCCESS' AND MATCHES > 0 order by SearchDateTime DESC", objCnn, 3,3

This code works, but ofcourse, the dates / times aren't sorted properly because the way it sorts text.

I tried using the convert function:
objRS.Open "SELECT DISTINCT TOP 5 SearchText, SearchType, [SearchDateTime] FROM " & logfileName & " WHERE USERNAME='" & Session("username") & "' AND SEARCHSTATUS='SUCCESS' AND MATCHES > 0 order by Convert(smalldatetime, [SearchDateTime],3)", objCnn, 3,3

but i got an error message that this function was recognized.
any suggestions.?

 

[MarkSweetland]

Two things:

I think if you order by the CONVERT() value, the function must also be in the list of columns in the SELECT clause if you specify SELECT DISTINCT.

Also, if you remove the formatting parameter from the CONVERT function, the conversion from the string value to smalldatetime should work as expected. Usually, you use the format options in CONVERT for display purposes, not ordering.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[dotolee]

can you give me an example of what you mean when you say to include the function in the list of columns?
do you mean something like:

objRS.Open "SELECT DISTINCT TOP 5 SearchText, SearchType, Convert([SearchDateTime]) FROM " & logfileName & " WHERE USERNAME='" & Session("username") & "' AND SEARCHSTATUS='SUCCESS' AND MATCHES > 0 order by Convert(smalldatetime, [SearchDateTime])", objCnn, 3,3

 

[MarkSweetland]

The same convert statement in the ORDER BY needs to be in the SELECT clause:

objRS.Open "SELECT DISTINCT TOP 5 SearchText, SearchType, Convert([red]smalldatetime,[/red][SearchDateTime]) FROM " & logfileName & " WHERE USERNAME='" & Session("username") & "' AND SEARCHSTATUS='SUCCESS' AND MATCHES > 0 order by Convert(smalldatetime, [SearchDateTime])", objCnn, 3,3


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach