[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

order by gametime

Posted on 2005-04-30
5
Medium Priority
?
197 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:gianitoo
  • 3
  • 2
5 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 13901561
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
0
 
LVL 1

Author Comment

by:gianitoo
ID: 13901613
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?
0
 
LVL 1

Author Comment

by:gianitoo
ID: 13901616
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
%>
0
 
LVL 34

Accepted Solution

by:
arbert earned 2000 total points
ID: 13901651
Redefine one field as your varchar (using the alias) and sort by the original column:

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 alphagametime, 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,gametime, cupschedule.location


Why are you using two columns for date time when it can be stored in one???
0
 
LVL 1

Author Comment

by:gianitoo
ID: 13901672
that is how the date was sent to me in excell and i just imported it in sql server
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question