Order by Date - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Order by Date

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2011-04-15 : 09:22:34

Hi All,
I have one table in which i have column row_date datetime. Data is storing like this
2010-09-13 10:23:52.127
2010-09-13 10:23:52.157
2010-09-13 10:23:52.157

Now i am running thuis query
Select convert(char,row_date,101),count(*) as dd from T1
group by convert(char,row_date,101)
Order by convert(char,row_date,101)

But it is not arranging dates in Ascending order

Please help

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-15 : 09:34:08

asc by what you are grouping this

Raghu' S

sanjay5219
Posting Yak Master

240 Posts

Posted - 2011-04-15 : 09:36:15

I am grouping by Date. When i am doing Order by convert(char,row_date,101) this is actually sorting by charactes e.g 1,11

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-04-17 : 02:06:06

why do you convert to dates to char? please do formatting at front end and get the date only as dates not varchar.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-17 : 19:02:03

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help. From your narrative, the only design for the table that will give a key is:

CREATE TABLE Silly_Timestamp_List
(foobar_date DATETIME NOT NULL PRIMARY KEY);

But your data is impossible to insert!

INSERT INTO Silly_Timestamp_List --- fails
VALUES
('2010-09-13 10:23:52.127'),
('2010-09-13 10:23:52.157'),
('2010-09-13 10:23:52.157');

This is not a table; it is deck of punch cards written in SQL. If you had read a book on RDBMS, you might have written a valid schema:

CREATE TABLE Proper_Timestamp_List
(foobar_date DATETIME NOT NULL PRIMARY KEY,
foobar_cnt INTEGER DEFAULT 1 NOT NULL );

We never use CONVERT in proper SQL; it changes temporal data types into strings for display. In RDBMS, all display work is done in the front end. Never in the database. Never in the database. Your query would be:

SELECT foobar_date
FROM Proper_Timestamp_List
ORDER BY foobar_date;

Please stop trying to code until you have educated yourself on the basics of RDBMS. You are dangerous to your employer.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources