July 10, 2003 at 1:35 am
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
July 10, 2003 at 2:22 am
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
July 10, 2003 at 4:57 am
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
July 10, 2003 at 5:07 am
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
July 10, 2003 at 5:23 am
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.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy