SQL Display time with seconds and AM PM

Instead of showing 12:00:00.0000000

I wanted to display 12:00:00 PM

There were some solutions built in for displaying 12:00 PM. However, I wanted the seconds to show as well.

Here are some ideas.  Grab the 2 right-hand most characters from the built in solution to get AM or PM.

/* Script by GlutenFree SQL, Daniel Gras, 1/18/2013 */
DECLARE @Time time
SET @Time = '12:00:00.0000000'

SELECT 
 CASE WHEN DATEPART(hour, @Time) in (0,10,11,12,22,23)
      THEN  LEFT( CONVERT(varchar(15),@Time,109 ),8) 
      ELSE LEFT( CONVERT(varchar(15),@Time,109 ),7) 
  END
  + ' ' + RIGHT( CONVERT(varchar(15),@Time,100 ) , 2)

One could also write a CASE statement to see if the time is >= 12:00:00.0000000, to decide PM or AM.   Test it out on your table with STATISTICS TIME ON or STATISTICS IO ON to see which one is better

/* Script by GlutenFree SQL, Daniel Gras, 1/18/2013 */
DECLARE @Time time
SET @Time = '12:00:00.0000000'

SELECT 
 CASE WHEN DATEPART(hour, @Time) in (0,10,11,12,22,23)
      THEN  LEFT( CONVERT(varchar(15),@Time,109 ),8) 
      ELSE LEFT( CONVERT(varchar(15),@Time,109 ),7) 
  END
  + CASE WHEN @Time >= '12:00:00.0000000' THEN ' PM' ELSE ' AM' END

Advertisements
This entry was posted in Business Intelligence and tagged . Bookmark the permalink.

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