Order by Time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Order by Time

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2011-04-14 : 05:40:03

Hi All,

I have one table T1 where i am storing this data as a Varchar. Now i want to show this data as order by. Means my first time should come first.

Please help

Appmt_TIme
08:00 AM
08:30 AM
09:00 AM
09:30 AM
07:30 AM
10:00 AM
11:00 AM
11:30 AM
12:00 PM
12.30 PM
10:30 AM
01:00 PM
01:30 PM

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-14 : 05:47:53

Hey try this
create table #t(Appmt_TIme Varchar(25))
insert into #t
select '08:00 AM' union all
select '08:30 AM' union all
select '09:00 AM' union all
select '09:30 AM' union all
select '07:30 AM' union all
select '12:00 PM' union all
select '12.30 PM' union all
select '10:30 AM' union all
select '01:00 PM' union all
select '01:30 PM'

select substring(Appmt_TIme,1,5) as t_Appmt_Time,substring(Appmt_TIme,6,7) as tt_Appmt_Time from #t order by tt_Appmt_Time,t_Appmt_Time

or

select t_Appmt_Time+''+tt_Appmt_Time from (select substring(Appmt_TIme,1,5) as t_Appmt_Time,substring(Appmt_TIme,6,7) as tt_Appmt_Time from #t) as t order by tt_Appmt_Time,t_Appmt_Time
Raghu' S

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-14 : 06:00:26

What version of SQL?

--
Gail Shaw
SQL Server MVP

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-14 : 06:01:09

If all the times are of same date then try this -

SELECT Appmt_TIme FROM #t
ORDER BY CONVERT( DATETIME, CONVERT ( VARCHAR(20), GETDATE(), 101) + ' ' + Appmt_TIme )

PS - 12.30 PM is typo error or value might be in this format ?
as it should be 12:30 PM

Vaibhav T

If I cant go back, I want to go fast...

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-04-14 : 06:33:01

select Appmt_TIme
from #t
order by convert(varchar(10),convert(datetime,Appmt_Time,101),108)

--Output:
Appmt_TIme
07:30 AM
08:00 AM
08:30 AM
09:00 AM
09:30 AM
10:30 AM
12:00 PM
12:30 PM
01:00 PM
01:30 PM

--Ranjit

sanjay5219
Posting Yak Master

240 Posts

Posted - 2011-04-14 : 06:55:10

Hi Ranjit,

I am getting below error
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(0 row(s) affected)

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-14 : 07:26:31

Reply to my post.
The problem is with data.
Ranjit's solution is correctly running.

Vaibhav T

If I cant go back, I want to go fast...

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-04-14 : 07:27:11

In your sample data check 12. 30 PM
it should be 12 : 30 PM

--Ranjit

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-04-14 : 07:29:53

create table #t(Appmt_TIme Varchar(25))
insert into #t
select '08:00 AM' union all
select '08:30 AM' union all
select '09:00 AM' union all
select '09:30 AM' union all
select '07:30 AM' union all
select '12:00 PM' union all
select '12:30 PM' union all -- not 12.30 PM(as like your sample data)
select '10:30 AM' union all
select '01:00 PM' union all
select '01:30 PM'

select Appmt_TIme
from #t
order by convert(varchar(10),convert(datetime,Appmt_Time,101),108)

--Ranjit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources