Time Portion Formating in SQL Server 2005

The simplest way to retrieve only time from datetime data type is converting datetime to varchar using date and time style code 108.

SELECT CONVERT(nvarchar,GETDATE(),108) AS "TIME"

Output:
14:35:43

Now, I need to format this output like 2:35:43 PM. After some googling, I learn, there is no Date Time Style Code for this kind of formated time. I find this following code snippet in Code Project. Just run it in your Query Window to see available Date Time Style Code and its Output.


declare @Loop int
set @Loop = -1
declare @table table( [Date] nvarchar(50) not null,    [Param] int not null)

while @Loop <= 150
begin
    set @Loop = @Loop + 1
    begin try    
     insert into @table select convert(nvarchar, getdate(), @Loop), @Loop    
    end try  
    begin catch        
     continue
    end catch
end
select * from @table

So, you have to write your own function to format this Time Output.



CREATE FUNCTION [dbo].[udf_GetFormattedTime](@timeInput varchar(8)) 
RETURNS varchar(11)
AS
BEGIN
IF @timeInput =! ''
BEGIN 
DECLARE @hourPortion int
DECLARE @timeOutput  varchar(11)
DECLARE @AMPM varchar(2)
SELECT @hourPortion = CAST(SUBSTRING(@timeInput,1,2)as int)
IF @hourPortion >= 12 
BEGIN
 IF @hourPortion > 12  
 BEGIN
  SET @hourPortion = @hourPortion -12
 END 
SET @AMPM = 'PM'
END
ELSE
BEGIN
 SET @AMPM = 'AM'
END
SELECT @timeOutput = CAST(@hourPortion as varchar(2))+ SUBSTRING(@timeInput,3,9)+ ' ' + @AMPM
END
ELSE
BEGIN
 SET @timeOutput = ''
END
RETURN @timeOutput
END

Use it Like -


SELECT dbo.udf_GetFormattedTime(CONVERT(nvarchar,GETDATE(),108))AS "FORMATED TIME"

Or

SELECT dbo.udf_GetFormattedTime('14:35:43')AS "FORMATED TIME"


Output:
2:35:43 PM

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: