?
Solved

tSQL Time Format & Order

Posted on 2004-11-03
10
Medium Priority
?
933 Views
Last Modified: 2008-03-10
How do you order by time in tSQL?

SELECT *
FROM BROADCASTS_TBL
ORDER BY time(broadcast_time)

broadcast_time field values examples:

4:30 PM EST
11:00 AM EDT
5:30 PM CST

Please provide an example of how this can be ordered in tSQL.

Thanks for the help!

Terry
0
Comment
Question by:splendorx
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 8

Expert Comment

by:sigmacon
ID: 12485583
What's the data type of these columns? If it's not datetime or smalldatetime, do:

SELECT *
FROM BROADCASTS_TBL
ORDER BY cast(broadcast_time as datetime)
0
 
LVL 8

Expert Comment

by:sigmacon
ID: 12485609
That's going to break with the time zone statements ... I have to give that more thought.
0
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 12485662
Maybe this
SELECT *
FROM BROADCASTS_TBL
ORDER BY Convert(dateTime, SubString(broadcast_time, 1, LEN(broadcast_time)-3))
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 8

Accepted Solution

by:
sigmacon earned 1000 total points
ID: 12485789
In order to get the (US) time zone format to be applied, you may need to do something like this:

order by
    case upper(ltrim(right(rtrim(broadcast_time), 4)))
        when 'AKST' then cast(left(broadcast_time, charindex('AKST', broadcast_time) - 1) as datetime) - 9.0 / 24
        when 'AST' then cast(left(broadcast_time, charindex('AST', broadcast_time) - 1) as datetime) - 4.0 / 24
        when 'CST' then cast(left(broadcast_time, charindex('CST', broadcast_time) - 1) as datetime) - 6.0 / 24
        when 'EST' then cast(left(broadcast_time, charindex('EST', broadcast_time) - 1) as datetime) - 5.0 / 24
        when 'HST' then cast(left(broadcast_time, charindex('HST', broadcast_time) - 1) as datetime) - 10.0 / 24
        when 'MST' then cast(left(broadcast_time, charindex('MST', broadcast_time) - 1) as datetime) - 7.0 / 24
        when 'PST' then cast(left(broadcast_time, charindex('PST', broadcast_time) - 1) as datetime) - 8.0 / 24
        when 'AKDT' then cast(left(broadcast_time, charindex('AKDT', broadcast_time) - 1) as datetime) - 8.0 / 24
        when 'ADT' then cast(left(broadcast_time, charindex('ADT', broadcast_time) - 1) as datetime) - 3.0 / 24
        when 'CDT' then cast(left(broadcast_time, charindex('CDT', broadcast_time) - 1) as datetime) - 5.0 / 24
        when 'EDT' then cast(left(broadcast_time, charindex('EDT', broadcast_time) - 1) as datetime) - 4.0 / 24
        when 'PDT' then cast(left(broadcast_time, charindex('PDT', broadcast_time) - 1) as datetime) - 7.0 / 24
        when 'MDT' then cast(left(broadcast_time, charindex('MDT', broadcast_time) - 1) as datetime) - 6.0 / 24
    end orderCol

0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12485797
maybee i am missing the point but surely

SELECT * FROM BROADCASTS_TBL
 order by broadcast_time

assuming broadcast_time  is a datetime datatype.
0
 
LVL 8

Expert Comment

by:sigmacon
ID: 12485840
Shogun, that's why I asked whether the column type is datetime ... ;-) ... but we haven't gotten an answer. Based on the data posted, I believe it's some character type, because datetime does not store time zone information as shown in the sample output - ASFAIK
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12485965
sorry sig,   didnt read your reply properly.  (i'll slap my wrist ;) )
0
 
LVL 1

Author Comment

by:splendorx
ID: 12486130
Sorry, the datatype is: nvarchar.
0
 
LVL 8

Expert Comment

by:sigmacon
ID: 12486143
Well, splendorx, did any of our answers help so far?
0
 
LVL 1

Author Comment

by:splendorx
ID: 12486781
Thanks for everyone's input.  sigmacon thanks for a great solution!!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question