• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5369
  • Last Modified:

AM/PM time from Date

I am capturing the hour from a date field.  I want to be able to sort by hour.  I think to get this, I need AM/PM for the hour.  This is what I currently have: datepart(hh,t.Time).  This returns 20,21,22,23,0,1,2,3.  I think I want 8:00 PM, 9:00 PM, 10:00 PM, 11:00 PM, 12:00 AM, 1:00 AM, 2:00 AM, 3:00 AM.  This will sort correctly, right?  

  • 3
  • 2
1 Solution
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.
TravidiaAuthor Commented:
"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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

TravidiaAuthor Commented:
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?
select cast(datepart(mm,mydate)as varchar(2)) + '/'+ cast(datepart(dd,logdate) as varchar(2))
 + '/' +cast(datepart(yy,logdate) as varchar(4)) +' ' +
cast(datepart(hh,logdate)  as varchar(2))+ right(logdate,2) thedate,logdate
 from mytable  

change all mydate to your date column and mytable to your table
create function convertTimeTo12Hr
	@time datetime

returns varchar(100)



/*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
	set @hour = @hour - 12
	set @ampm = 'PM'

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

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


Open in new window


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now