The following function can be used to format a datetime string stored in SQLServer to a Short Time String using the following fromat hh:mm am/pm
Usage
Select dbo.fn_formatTime(fieldname) as Time from SomeTable
CREATE function [dbo].[fn_FormatTime]
(@time as varchar(300))
Returns Varchar(100)
as
begin
declare @x varchar(100)
declare @sufix char(3)
if isdate(@time)=1
begin
declare @time1 datetime
set @time1=cast(@time as datetime)
if datepart(hh,@time1)>11
begin
set @sufix=' pm'
end
else
begin
set @sufix=' am'
end
if datepart(hh,@time1)>12
begin
set @x = convert(varchar(20),dateadd(hh,-12,@time1),8)
set @x = left(@x,5)+ @sufix
end
else
begin
set @x = left(convert(varchar(20),@time1,8),5) + @sufix
end
SET @X = replace(replace(replace(replace(replace(replace(replace(replace(replace(@x,'01:','1:'),'02:','2:'),'03:','3:'),'04:','4:'),'05:','5:'),'06:','6:'),'07:','7:'),'08:','8:'),'09:','9:')
end
else
begin
set @x=@time
end
return @x
end