Solved

tSQL Time Format & Order

Posted on 2004-11-03
917 Views
How do you order by time in tSQL?

SELECT *

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
Question by:splendorx

LVL 8

Expert Comment

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

SELECT *
0

LVL 8

Expert Comment

That's going to break with the time zone statements ... I have to give that more thought.
0

LVL 23

Expert Comment

Maybe this
SELECT *
0

LVL 8

Accepted Solution

In order to get the (US) time zone format to be applied, you may need to do something like this:

order by
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 '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

maybee i am missing the point but surely

assuming broadcast_time  is a datetime datatype.
0

LVL 8

Expert Comment

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

0

LVL 1

Author Comment

Sorry, the datatype is: nvarchar.
0

LVL 8

Expert Comment

Well, splendorx, did any of our answers help so far?
0

LVL 1

Author Comment

Thanks for everyone's input.  sigmacon thanks for a great solution!!
0

Featured Post

This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.