Sorting a date column

• k_sekhar_rao

  Grasshopper

  Points: 18

  More actions

  July 10, 2003 at 1:35 am

  #81945

  hi all,

  I have query which diplays require information for my application. Query is given below:

  SELECT distinct POProjects.ProjectId,POProjects.PINNo,POProjects.CustName,POProjects.ProjName,

  convert(varchar(10),POProjects.ProjectStartDate,103) ProjectStartDate,

  convert(varchar(10),POProjects.ProjectEndDate,103) ProjectEndDate,

  dbo.get_emp_name(POProjects.ProjHeadAlias) ProjectHead,dbo.Get_All_PM_Names(POProjects.ProjectId) ProjectManager, dbo.get_emp_name(POProjects.GroupheadAlias)GroupHead,POProjects.ProjType,POProjects.EstPersonMonths,POProjects.ProjectStatus,POProjects.PeakTeamSize PeakTeamSize,dbo.Technologylist(POProjects.ProjectId) Technology FROM POProjects,POProjectPMs where POProjects.ProjectId=POProjectPMs.ProjectId

  order by POProjects.ProjectStartDate

  Problem is that, though i am using order by statement to sort the projectstartdate column, i am not getting the desired results. Start date and endate are declared as datetime datatypes. I am using convert function since i need only the date part in my application. Can any one suggest me regarding this.

  Thanks in advance,

  sekhar

• mccork

  SSC-Insane

  Points: 22288

  More actions

  July 10, 2003 at 2:22 am

  #464769

  Your query looks ok. When you say you're not getting the desired results, what do you mean? Not the required ordering? Not getting the required output formats?

  
  Cheers,
  - Mark

• k_sekhar_rao

  Grasshopper

  Points: 18

  More actions

  July 10, 2003 at 4:57 am

  #464784

  Thanks mccork,

  i am getting results in correct format..i mean date format. But that column is not ordered in asc/desc, according my requirement.

  Thanks,

  sekhar

  Edited by - k_sekhar_rao on 07/10/2003 05:02:03 AM

• mccork

  SSC-Insane

  Points: 22288

  More actions

  July 10, 2003 at 5:07 am

  #464787

  I can't see why they would be displayed in dd/mm/yyyy format, order from earliest to latest. Can you post an example of out of sequence dates?

  
  Cheers,
  - Mark

• Antares686

  SSC Guru

  Points: 125444

  More actions

  July 10, 2003 at 5:23 am

  #464798

  The problem is it is order by character order because POProjects.ProjectStartDate is a varchar. This happens because the last thing that occurrs is the Order By and because you used the same name it sees the varchar value.

  So

  1/1/2002

  1/2/2003

  1/3/2002

  2/2/2000

  etc

  is what you get

  To order like you want change

  order by POProjects.ProjectStartDate

  to

  order by cast(POProjects.ProjectStartDate as datetime) asc

  basically make sure it is sorting as a date value not a character.

  Or use another alias name besides ProjectStartDate and it won't be an issue.