September 27, 2005 at 10:41 am
I have done some searching around and can't seem to find it.
I have a datetime field and only care about the time portion of it. How do I get the query to order by just the time and ignore the date?
September 27, 2005 at 10:53 am
use datepart function and return the time portions and order by these
September 27, 2005 at 10:54 am
This will work too:
create table #temptime (pk int identity, dt datetime)
insert into #temptime (dt)
values (getDate())
insert into #temptime (dt)
values (dateadd(ss,-100000,getDate()))
insert into #temptime (dt)
values (dateadd(ss,100000,getDate()))
insert into #temptime (dt)
values (dateadd(dd,-1,getDate()))
insert into #temptime (dt)
values (dateadd(hh,-1,getDate()))
insert into #temptime (dt)
values (dateadd(ss,-100,getDate()))
insert into #temptime (dt)
values (dateadd(hh,10,getDate()))
select
dt,
theTime = right(convert(varchar(24),dt,113),12)
from #temptime
order by right(convert(varchar(24),dt,113),12)
September 27, 2005 at 11:13 am
I decided to use the datepart function and this query works in the query analyzer however I get this error
Error 1023 Invalid Parameter 1 specified for datepart
datepart("hh", dsd.start_time)
September 27, 2005 at 11:15 am
You don't need the double quotes.
I wasn't born stupid - I had to study.
September 27, 2005 at 11:22 am
You might want to use minutes (or even ms if it's a datetime (not smalldatetime) for the order by instead of hours... if you want ot keep the true order of the data.
September 27, 2005 at 11:23 am
thank you I don't believe I didn't mess with the quotes usually the first thing I do.
Thanks again all who helped
September 28, 2005 at 12:36 pm
The easiest way would be to use the convert as above but use switch 8 instead of 113. That will give you just hh:mm:ss. If you need milliseconds you can use 14.
SELECT CONVERT(VARCHAR,GetDate(),8)
Gary Johnson
Sr Database Engineer
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply