asked on

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

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

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

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

Start Free Trial

SOLUTION

_agx_

membership

This solution is only available to members.

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

Start Free Trial

TheCommunicator

ASKER

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

SOLUTION

Sharath S

membership

This solution is only available to members.

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

Start Free Trial

_agx_

Is the data type of the column is datetime?

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

Alpesh Patel

order by convert(varchar, getdate(), 109)

Anthony Perkins

PatelAlpesh,
>>order by convert(varchar, getdate(), 109) <<
Just out of curiousity do you actually understand what you write?

TheCommunicator

ASKER

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?

SOLUTION

Sharath S

membership

This solution is only available to members.

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

Start Free Trial

TheCommunicator

ASKER

Ohhhh i got it. Thanks. Yeah I just missed that comment. Thanks.