asked on

order by gametime

how come my query does not sort by gametime?

SELECT teams.teamid AS Expr1, teams_1.teamid AS Expr2, teams.Teamname AS hometeam, teams_1.Teamname AS visitteam, teams.Agegroup AS age, teams.Gender AS gender, cupschedule.Game#, RIGHT(CONVERT(VARCHAR(19), cupschedule.GameTime, 100), 7) as gametime, cupschedule.GameDate, cupschedule.HomeScore, cupschedule.VisitScore, cupschedule.Location, cupschedule.Cup,cupschedule.status
FROM cupschedule INNER JOIN teams ON cupschedule.HomeID = teams.teamid INNER JOIN teams teams_1 ON cupschedule.VisitID = teams_1.teamid
WHERE (cupschedule.Cup like 'varcup') AND (cupschedule.Location like 'varlocation') AND (teams.Agegroup like 'varage') AND (teams.Gender like 'vargender')
ORDER BY cupschedule.gamedate,cupschedule.gametime,cupschedule.location

arbert

You're converting it to a varchar so it's sorting alpha instead of true time--when you look at the results, is it sorted in alpha order?

Also, on your LIKE statements--don't use LIKE if you aren't actually performing a LIKE (you don't have a % or any other like matching symbol, so you're basically doing an = instead of a like).

Brett

gianitoo

ASKER

so what are my options? the reason why i converted it to varchar is because the time displays like this 1/1/1900 10:00:00 AM and in my asp page i need to display it like this 10:00:00 AM to avoid confusion. what can i do instead so i can sort by time?

gianitoo

ASKER

and i am using like but you wont see it because i did not post the code

<%
Dim Recordset1__varcup
Recordset1__varcup = "%"
If (Request.Form("cup") <> "") Then
Recordset1__varcup = Request.Form("cup")
End If
%>
<%
Dim Recordset1__varage
Recordset1__varage = "%"
If (Request.Form("age") <> "") Then
Recordset1__varage = Request.Form("age")
End If
%>
<%
Dim Recordset1__vargender
Recordset1__vargender = "%"
If (Request.Form("gender") <> "") Then
Recordset1__vargender = Request.Form("gender")
End If
%>
<%
Dim Recordset1__varlocation
Recordset1__varlocation = "%"
If (Request.Form("location") <> "") Then
Recordset1__varlocation = Request.Form("location")
End If
%>
<%
Dim Recordset1__vardia
Recordset1__vardia = "1/1/05"
If (Request.Form("dia") <> "") Then
Recordset1__vardia = Request.Form("dia")
End If
%>
<%
Dim Recordset1__varstatus
Recordset1__varstatus = "%"
If (Request.Form("status") <> "") Then
Recordset1__varstatus = Request.Form("status")
End If
%>

ASKER CERTIFIED SOLUTION

arbert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gianitoo

ASKER

that is how the date was sent to me in excell and i just imported it in sql server