sql server - TSQL : Issue in converting 24 hour time into AM PM -

sql server - TSQL : Issue in converting 24 hour time into AM PM -

i have made function convert 24 hr time (for e.g 0900 9 am). here function.

begin declare @ret varchar(10); declare @temp varchar(10); declare @temphh varchar(10); declare @ampm varchar(10); declare @temphours int; set @ret = cast(@timing varchar); set @temp = @ret; if len(@timing) = 3 begin set @ret = '0' + cast(left(@temp,1) varchar(5)) + ':' set @ret = @ret + right(@temp,2) + ' am'; end -- if len = 3 if len(@timing) = 4 begin set @temphours = cast(left(@temp,2) int); set @ampm = ' am'; if @temphours > 12 begin set @ampm = ' pm'; set @temphours = @temphours - 12; end if len(@temphours)=1 set @ret = '0'+ cast(@temphours varchar(5)) + ':' else set @ret = cast(@temphours varchar(5)) + ':' set @ret = @ret + right(@temp,2) + @ampm; end --length = 4 if len(@ret)=2 set @ret = '00:'+ @ret; homecoming @ret; end

it works fine, times except 1200 1259. e.g shows "12:30 am" 1230, or "12:10 am" 1210 etc, should show "12:30 pm" , "12:10 pm" respectively. how can solve this?

you should replace code:

if @temphours > 12 begin set @ampm = ' pm'; set @temphours = @temphours - 12; end

with:

if @temphours >= 12 set @ampm = ' pm'; if @temphours > 12 set @temphours = @temphours - 12;

sql-server function tsql time