XML FeedsThis 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
-----------------
16:20:01.8000000
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)
--------------------
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
- SELECT STUFF('4:20PM',5,0,' ')
---------------
4:20 PM
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
--------------
4:20 PM
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,' ')
--------------
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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
I'm afraid the approach will only work for times up to 9:59:59 AM/PM. Once you get into 2-digit hours (10, 11 or 12), then the function will break, I'm afraid. (For a time of 10:00:00, it will show '0:00 AM', cutting off the left-most digit).
Thanks Brad, I will modify the code...I could have sworn I tested with that :-( 
"Okay but what if you are not on SQL Server 2008? No problem really, it is almost the same"
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.