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
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.
8 Comments
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).
Instead of RIGHT(,6), I would instead to RIGHT(,7) and then LTRIM() it at the end. You’ll also have to change the location of the STUFF(). Here’s what I suggest:
SELECT LTRIM(STUFF(RIGHT(CONVERT(VARCHAR(30),@d,100),7),6,0,’ ‘))
–Brad
Thanks Brad, I will modify the code…I could have sworn I tested with that 🙁
Brad, I solved it buy using RIGHT(‘ ‘ + …)
example
DECLARE @t TIME = ‘2010-03-24 22:20:01.800’
select STUFF(RIGHT(‘ ‘ + CONVERT(VARCHAR(30),@t,100),7),6,0,’ ‘)
DECLARE @d DATETIME = ‘2010-03-24 22:20:01.800’
select STUFF(RIGHT(‘ ‘ + CONVERT(VARCHAR(30),@d,100),7),6,0,’ ‘)
GO
DECLARE @t TIME = ‘2010-03-24 02:20:01.800’
select STUFF(RIGHT(‘ ‘ + CONVERT(VARCHAR(30),@t,100),7),6,0,’ ‘)
DECLARE @d DATETIME = ‘2010-03-24 02:20:01.800’
select STUFF(RIGHT(‘ ‘ + CONVERT(VARCHAR(30),@d,100),7),6,0,’ ‘)
“Okay but what if you are not on SQL Server 2008? No problem really, it is almost the same”
Denis, you’ll need to amend your first line to not set the value “inline” as well otherwise you’ll get the “cannot assign a default value to a local variable” error.
Yes, you are right, can you tell I only have 2008 instances 🙂
I have fixed it
Hi
Could you help me, I want convert this column(sndtime) just time but I can’t this is code that I’m using.
Code
LTRIM(STUFF(RIGHT(CONVERT(VARCHAR(30),tsd.sndTime,100),7),6,0,)) AS [Scan Time]
sndTime
1900-01-01 22:36:00.000
I have fixed it.
Gracias
Thanks for the information. I expected this to be much more difficult. BTW, using 2005 and all of my times are working correctly with just:
.