|
Home > Archive > microsoft.public.sqlserver.server > June 2002 > Order By Time??
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Jeff Swanberg 2002-06-24, 9:25 pm |
| I have a query that has an Order By clause that references a smalldatetime
field. The problem is that the result set of the query puts records with a
12:00 PM time before those with a 9:00 AM time. What's the proper syntax to
get the result set in the proper order??
Jeff
| |
| Steve Kass 2002-06-24, 9:25 pm |
| Jeff,
Can you give an example with data (create table ..., insert into .. values)
to show this? Something else must be going on, because 12:00 pm comes
after 9:00 am, not before (assuming the date parts are the same):
create table Jeff (
t smalldatetime
)
insert into Jeff values ('12:00pm')
insert into Jeff values ('8:00am')
select right(convert(varchar(30),t,0)
,7) as t from Jeff order by t
go
drop table Jeff
It's very hard to offer any suggestions to such a general question. If you
can create tables and data to insert, and then show a query that seems not
to work, I'm sure someone will help.
Steve Kass
Drew University
Jeff Swanberg wrote:
> I have a query that has an Order By clause that references a smalldatetime
> field. The problem is that the result set of the query puts records with a
> 12:00 PM time before those with a 9:00 AM time. What's the proper syntax to
> get the result set in the proper order??
>
> Jeff
| |
| lindawie 2002-06-24, 9:25 pm |
| Jeff,
> I have a query that has an Order By clause that references a
> smalldatetime field. The problem is that the result set of the query
> puts records with a 12:00 PM time before those with a 9:00 AM time.
> What's the proper syntax to get the result set in the proper order??
What does your order by clause look like?
You could try something like this:
create table t1 (c1 int, c2 datetime)
insert t1 select 1, convert(datetime, '12:00:00', 108)
insert t1 select 2, convert(datetime, '09:00:00', 108)
insert t1 select 3, convert(datetime, '00:00:00', 108)
insert t1 select 4, convert(datetime, '21:00:00', 108)
select c1, convert(varchar, c2, 100), convert(varchar, c2, 108), c2
from t1
order by convert(varchar, c2, 108)
go
drop table t1
Linda
| |
| Ron K 2002-06-25, 10:25 am |
| Be sure your "order by" is referencing the database column that contains the
date and not a formatted date that you retrieved in the select statement for
display. The formatted date is only a string and will be sorted character
by character and not as a date field.
Ron
"Jeff Swanberg" <jswanberg@swanbergcomputing.com> wrote in message
news:eEdUhG$GCHA.1716@tkmsftngp08...
> I have a query that has an Order By clause that references a smalldatetime
> field. The problem is that the result set of the query puts records with
a
> 12:00 PM time before those with a 9:00 AM time. What's the proper syntax
to
> get the result set in the proper order??
>
> Jeff
>
>
|
|
|
|
|