Converting time while retaining leading zero

Question

I am attemping to convert a datetime value to HH:MM PM or AM without losing the leading on a single digit hour.  I almost have it worked out but the leading zero is stumping me.  If someone knows a better way to do this even better, thanks you

SELECT

STUFF(RIGHT(' ' + CONVERT(VARCHAR(30),GETDATE(),100),7),6,0,' ') AS FormattedTime

Answer 1

Check

Formatting the time from a datetime or time datatype by using the STUFF function

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

If you are on SQL 2012 or later then

SELECT Right('0' + Format(GetDate(), 't'), 8) AS FormattedTime

If you are on an earlier version, then

SELECT Stuff(Right('0' + LTRIM(RIGHT(CONVERT(VARCHAR(30),GetDate(),100), 7)), 7), 6, 0, ' ') AS FormattedTime

Tom

Answer 3

SELECT CONVERT(VARCHAR(5),GETDATE(),114)+' '+  RIGHT(CONVERT(VARCHAR(30),GETDATE(),100),2) AS FormattedTime

Answer 4

Here's a solution:

SELECT SUBSTRING(STUFF(REPLACE(CONVERT(CHAR(19),CURRENT_TIMESTAMP,100),' ','0'),18,0,' '),13,8)

---

Gert-Jan