Converting time while retaining leading zero

Converting time while retaining leading zero

• Thursday, February 07, 2013 4:16 AM

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

All Replies

• Thursday, February 07, 2013 4:21 AM
Moderator

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:54 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

• Thursday, February 07, 2013 2:51 PM
Moderator

`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 9:10 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
•