Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

converting a string to date

Status
Not open for further replies.

dotolee

Technical User
Jan 27, 2008
134
CA
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.?
 

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

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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top