order by date problem - 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

SQL Server 2000 Forums

Transact-SQL (2000)

order by date problem

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2007-04-11 : 18:23:47

hi all

i got an order by date(datetime collum) but it messes up the years! any idea?

tnx,

mike

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-11 : 18:31:19

You haven't provided enough information for us to help you. Please post a data example of what the problem is.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-11 : 19:06:30

quote:
Originally posted by mike13

hi all

i got an order by date(datetime collum) but it messes up the years! any idea?

tnx,

mike

Are you using varchar to store date ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-12 : 01:31:22

quote:
Originally posted by mike13

i got an order by date(datetime collum) but it messes up the years! any idea?

Show us the query.

Peter Larsson
Helsingborg, Sweden

mike13
Posting Yak Master

219 Posts

Posted - 2007-04-12 : 03:03:40

hi

i see where i got my problem
SELECT TOP (100) PERCENT COUNT(*) AS COUNT, date
FROM (SELECT CONVERT(CHAR(8), date, 10) AS date
FROM dbo.T_Static_page) AS TSP
GROUP BY date
ORDER BY date

this gives this result

1885 01-01-07
1831 01-02-07
3292 01-03-07
1607 01-04-07
949 01-05-07
1880 01-06-07
1412 01-07-07

if i do normal select, i get the time with it, which i dont want and prevents me to do the group by day.
ex:
SELECT TOP (100) PERCENT COUNT(*) AS COUNT, date
FROM (SELECT date
FROM dbo.T_Static_page) AS TSP
GROUP BY date
ORDER BY date DESC

1 12-4-2007 9:02:30
1 12-4-2007 9:02:20
1 12-4-2007 9:02:01
1 12-4-2007 9:01:53
1 12-4-2007 9:01:15
1 12-4-2007 9:00:59
1 12-4-2007 9:00:49
1 12-4-2007 8:59:35

any suggestions?

tnx a lot,

mike

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-12 : 03:06:45