# 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
Thursday, February 07, 2013 4:16 AM

• 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

• Proposed as answer by Thursday, February 07, 2013 5:06 AM
• Marked as answer by Friday, February 08, 2013 3:22 AM
Thursday, February 07, 2013 4:54 AM
• 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

Thursday, February 07, 2013 4:21 AM
• Here's a solution:

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

Gert-Jan

• Marked as answer by Friday, February 08, 2013 3:28 AM
Thursday, February 07, 2013 9:10 PM

• 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

Thursday, February 07, 2013 4:21 AM
• 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

• Proposed as answer by Thursday, February 07, 2013 5:06 AM
• Marked as answer by Friday, February 08, 2013 3:22 AM
Thursday, February 07, 2013 4:54 AM
• `SELECT CONVERT(VARCHAR(5),GETDATE(),114)+' '+  RIGHT(CONVERT(VARCHAR(30),GETDATE(),100),2) AS FormattedTime`

• Proposed as answer by Thursday, February 07, 2013 3:57 PM
Thursday, February 07, 2013 2:51 PM
• Here's a solution:

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

Gert-Jan

• Marked as answer by Friday, February 08, 2013 3:28 AM
Thursday, February 07, 2013 9:10 PM