order by just time

  • 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?

  • use datepart function and return the time portions and order by these

  • 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)

  • 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)

  • You don't need the double quotes. 

    I wasn't born stupid - I had to study.

  • 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.

  • thank you I don't believe I didn't mess with the quotes usually the first thing I do.

     

     

    Thanks again all who helped

  • 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