?
Solved

Dates are sorted in wrong manner while trying to wirk with Order by on dateTime field

Posted on 2011-05-05
10
Medium Priority
?
187 Views
Last Modified: 2012-06-21
Hello guys,

I am putting an order by clause on a datetime field and when I try to do this t sorts the date in a way where all the am/Pm are side by side.

I mean date are not sorted the way they are but they are ordered like following

May  3 2011 12:00AM	0	CF1
May  2 2011 12:00PM	0	CF1
May  2 2011 12:00AM	0	CF1
May  2 2011 11:00PM	0	CF1
May  2 2011 11:00AM	2	CF1
May  2 2011 10:00PM	0	CF1
May  2 2011 10:00AM	0	CF1
May  2 2011  9:00PM	0	CF1

Open in new window


Thanks
0
Comment
Question by:TheCommunicator
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 500 total points
ID: 35702573
try using

ORDER BY CONVERT(datetime, yourColumnName, 109)
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 500 total points
ID: 35702667
That shouldn't happen with a true datetime column.  Sounds like your column is some sort of varchar ...
0
 

Author Comment

by:TheCommunicator
ID: 35702737
CONVERT(datetime, yourColumnName, 109)

(No column name)	BannerCount	Bannername
2011-05-03 00:00:00.000	0	CF1
2011-05-02 12:00:00.000	0	CF1
2011-05-02 00:00:00.000	0	CF1
2011-05-02 23:00:00.000	0	CF1
2011-05-02 11:00:00.000	2	CF1
2011-05-02 22:00:00.000	0	CF1
2011-05-02 10:00:00.000	0	CF1
2011-05-02 21:00:00.000	0	CF1
2011-05-02 09:00:00.000	0	CF1

Open in new window

0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1000 total points
ID: 35702833
Is the data type of the column is datetime? If not, try converting it to datetime and then order by that.

ORDER BY CONVERT(datetime, yourColumnName)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35703451
Is the data type of the column is datetime?

... also can you post the exact ORDER BY clause you're using?  
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35704709
order by convert(varchar, getdate(), 109)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35710729
PatelAlpesh,
>>order by convert(varchar, getdate(), 109) <<
Just out of curiousity do you actually understand what you write?
0
 

Author Comment

by:TheCommunicator
ID: 35730904
Hi Guys,

I am so sorry for all the Hoopla. data was actually travelling from original table to a temp table and getting sorted and all. Temptable had been defined to have a varchar() column there  and that was the reason of this not getting sorted properly. Once I changed the data type from varchar to date time, it started working great.

BUT

The fact that I was using Convert(datetime, mycolumnname) while doing selection, it should actually convert it into the dateTime first and then sort it.

HOW is converting at selection time different than declaring column as datetime?
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1000 total points
ID: 35730931
>> The fact that I was using Convert(datetime, mycolumnname) while doing selection, it should actually convert it into the dateTime first and then sort it.
It won't sort if you don't sort on the converted value. As I stated, you have to user "ORDER BY CONVERT(datetime, yourColumnName)"
0
 

Author Comment

by:TheCommunicator
ID: 35730964
Ohhhh i got it. Thanks. Yeah I just missed that comment. Thanks.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

839 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