in datatable date sorting is not valid.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

in datatable date sorting is not valid.

Author

Topic

ali_786
Starting Member

3 Posts

Posted - 2008-06-11 : 03:45:35

=======================================================================
Invalid sorting date column in .net
-------------------------------------------------------

Hi i am using MS SQL Server 2000 on back end and on front end i am using ASP.NET to show the data while accessing data from table i want to sort date in descending order but it shows below invalid order.

4/10/2008
4/10/2008
4/10/2008
4/15/2008
4/15/2008
4/9/2008
4/9/2008
4/9/2008
9/10/2007

the exact order is

4/9/2008
4/9/2008
4/9/2008
4/10/2008
4/10/2008
4/10/2008
4/15/2008
4/15/2008
9/10/2007

which is not coming in front so if any one have idea plz guide me
Regards.
Ali.

webmaster http://www.GlobalGuideLine.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 03:50:52

The sorting is correct. According to string sorting.

Replace your query in the database as

select Col1 from table1 order by convert(datetime, col1, 101)

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 03:51:38

Are you specifying explicitly the order required using ORDER BY clause? Also is you date field having datatype of datetime?

johnsql
Posting Yak Master

161 Posts

Posted - 2008-06-11 : 14:17:38


declare @t table(dt datetime)

insert @t
select '4/10/2008'
union all select '4/10/2008'
union all select '4/10/2008'
union all select '4/15/2008'
union all select '4/15/2008'
union all select '4/9/2008'
union all select '4/9/2008'
union all select '4/9/2008'
union all select '9/10/2007'

--
select * from @t
order by year(dt) desc, day(dt), month(dt)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-06-11 : 15:57:13

johnsql -- why would you do it that way? That is completely unnecessary and inefficient. Simply ORDER BY the datetime column. that's it.

The problem the original poster is having is a simple one: He is not using correct data types, whether it is how he is storing his dates, or how he is returning them in a SELECT, or how he has his DataColumn declared.

see: http://weblogs.sqlteam.com/jeffs/archive/2007/07/03/60248.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources