AM/PM time from Date

If you use datepart the output will always be an integer (there is no way around).  Sorting by the integer will give you the hours sorted correctly just in military time.  http://msdn2.microsoft.com/en-us/library/ms174420.aspx

If you want to see the AM PM you would need to use the RIght(item,# of string I want to see) function.

"If you want to see the AM PM you would need to use the RIght(item,# of string I want to see) function."

What is this function?  How do I organize it in my query?

Try running this so you can get an example of the outputs I was mentioning:
select CAST( time AS FLOAT ) thedate,time,right(time,7) from t order by thedate

The first item is a numeric rep. of the decimal part of the date
the second is your plane date
the third is the time with AM/PM stamp.

I am pretty sure you won't be able to sort the items the way you want with either of these methods.  Instead I would do a modification on what you initially did:
select datepart(hh,time) hour,datepart(mi,time) minute,
right(time,2) AMPM,time,right(time,7)
 from t  order by hour,minute,ampm

There you are extracting the hour, minute and ampm and then sorting in that order.  I included the whole date and the time portion as well so you can see it is sorting correctly

I can't get that to work.  Can you show me how to extract the date and time only showing the hour?  
Like this: 8/1/2007 10:00 PM with the minutes as 00?

create function convertTimeTo12Hr
(
	@time datetime
)

returns varchar(100)

as

begin

/*declare @time as datetime
set @time = '1/1/2011 16:01:00'*/

declare @hour varchar(5) = DATEPART(HOUR, @time)
declare @minute varchar(2) = datepart(Minute, @time)
declare @ampm varchar(2) = 'AM'

if DATEPART(HOUR, @time) > 12
begin
	set @hour = @hour - 12
	set @ampm = 'PM'
end

if @minute < 10
begin
	set @minute = '0' + @minute
end

--print @hour + ':' + @minute + ' ' + @ampm
return @hour + ':' + @minute + ' ' + @ampm

end

Select allOpen in new window