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
DECLARE @t TIME = '2010-03-24 16:20:01.800' SELECT @t AS TIME
Okay that gives me 16:20:01.8000000, not what the original poster wanted
You can do this
DECLARE @t TIME = '2010-03-24 16:20:01.800' SELECT CONVERT(VARCHAR(30),@t,100)
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
SELECT STUFF('4:20PM',5,0,' ')
Now let’s use the code from before and wrap the STUFF function around it
DECLARE @t TIME = '2010-03-24 16:20:01.800' SELECT STUFF(RIGHT(' ' + CONVERT(VARCHAR(30),@t,100),7),6,0,' ') AS FormattedTime
Okay but what if you are not on SQL Server 2008? No problem really, it is almost the same
DECLARE @d datetime SET @d = '2010-03-24 16:20:01.800' SELECT STUFF(RIGHT(' ' + CONVERT(VARCHAR(30),@d,100),7),6,0,' ')