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 AMModerator
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- Proposed As Answer by Alexey KnyazevMVP Thursday, February 07, 2013 4:54 AM
- Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Monday, February 11, 2013 7:36 AM
-
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
- Proposed As Answer by Latheesh NKMicrosoft Community Contributor Thursday, February 07, 2013 5:06 AM
- Marked As Answer by spree311 Friday, February 08, 2013 3:22 AM
-
Thursday, February 07, 2013 2:51 PMModerator
SELECT CONVERT(VARCHAR(5),GETDATE(),114)+' '+ RIGHT(CONVERT(VARCHAR(30),GETDATE(),100),2) AS FormattedTime
- Proposed As Answer by Naarasimha 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 spree311 Friday, February 08, 2013 3:28 AM
.png)
