This is a quick post about a question that had to do with time formatting. This question was asked in our SQL Server programming forum today

I don’t see a format of hh:mm AM/PM only

Do we need to do it using datepart or how to get only hour:minute am/pm format ?

The question can be found here: Formatting time only. SQL Server has a bunch of formats for converting datetime values, a list of all of them can be found in Books On Line here: CAST and CONVERT (Transact-SQL)

So let’s say for example that you have this datetime ‘2010-03-24 16:20:01.800’ and you want to show 4:20 PM, how can you do that?

Let’s take a look, if you are on SQL Server 2008 you can convert to time

T-SQL
1
2

DECLARE @t TIME = '2010-03-24 16:20:01.800'
SELECT @t AS TIME
DECLARE @t TIME = '2010-03-24 16:20:01.800'
SELECT @t AS TIME

—————–
16:20:01.8000000

Okay that gives me 16:20:01.8000000, not what the original poster wanted

You can do this

T-SQL
1
2

DECLARE @t TIME = '2010-03-24 16:20:01.800'
SELECT CONVERT(VARCHAR(30),@t,100)
DECLARE @t TIME = '2010-03-24 16:20:01.800'
SELECT CONVERT(VARCHAR(30),@t,100)

——————–
4:20PM

That gives me 4:20PM. But how can I add a space between 0 and PM? Say hello to my little friend STUFF. In order to add a space you need to start at position 5 and add a space and don’t do any replacement.

Take a look at what I mean

T-SQL
1

SELECT STUFF('4:20PM',5,0,' ')
SELECT STUFF('4:20PM',5,0,' ')

—————
4:20 PM

Now let’s use the code from before and wrap the STUFF function around it

T-SQL
1
2

DECLARE @t TIME = '2010-03-24 16:20:01.800'
SELECT STUFF(RIGHT(' ' + CONVERT(VARCHAR(30),@t,100),7),6,0,' ') AS FormattedTime
DECLARE @t TIME = '2010-03-24 16:20:01.800'
SELECT STUFF(RIGHT(' ' + CONVERT(VARCHAR(30),@t,100),7),6,0,' ') AS FormattedTime

————–
4:20 PM

Okay but what if you are not on SQL Server 2008? No problem really, it is almost the same

T-SQL
1
2
3

DECLARE @d datetime 
SET @d = '2010-03-24 16:20:01.800'
SELECT STUFF(RIGHT(' ' + CONVERT(VARCHAR(30),@d,100),7),6,0,' ')
DECLARE @d datetime 
SET @d = '2010-03-24 16:20:01.800'
SELECT STUFF(RIGHT(' ' + CONVERT(VARCHAR(30),@d,100),7),6,0,' ')

————–
4:20 PM

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

Related Posts