How do i alter the code below to pad the minute and seconds with a 0 when it is a single digit?

Question

The code below is doing just what i want it to do, as far as converting the time to a format that works for my needs. However, when the minutes and seconds are in the single digit, the format is not looking good. I would like to adjust the code to pad the minutes and seconds where it is needed. Any suggestions? Again, i would like to stay with the existing code as much as possible.
Thank you!

SELECT SUBSTRING(CONVERT(CHAR(14), DateTimeRaw, 100), 13, 2)  + ':' + 
CONVERT(nvarchar, DATEPART(minute, DateTimeRaw)) + ':' + 
CONVERT(nvarchar, DATEPART(second, 
DateTimeRaw)) + '.' + CONVERT(nvarchar, DATEPART(ms, DateTimeRaw) / 100)
 + ' ' + CONVERT(nvarchar, CASE WHEN datepart(hour, DateTimeRaw) 
< 12 THEN 'AM' ELSE 'PM' END)   AS AGMPLUSMSTIME
FROM RAW

Use LPAD(string,2,'0'). However I would suggest not using SQL for your string manipulations if possible. — Danny, Nov 22 '11 at 15:18
John you really should use my solution, it solves all your problems and it ie easy to maintain — t-clausen.dk, Nov 22 '11 at 19:52

score 4 · Answer 1 · answered Nov 22 '11 at 15:17

4

RIGHT('00' + CONVERT(nvarchar, DATEPART(minute, DateTimeRaw)), 2)

Basically: concatenate 2 zeroes with your time value, ending up with something like 001, 0023, or 0059, then take the 2 right-most characters, leaving you with 01, 23, 59 respectively.

answered Nov 22 '11 at 15:17

Marc B

356,200
43
426
500

That makes since! I am trying it now. – John Nov 22 '11 at 15:23

score 0 · Answer 2 · answered Nov 22 '11 at 15:35

0

How about this:

select right(stuff(convert(varchar(26), getdate(), 9), 23,2, ' '), 13)

Result:

 4:35:15:4 PM

I used getdate() instead of your field, just as an example

answered Nov 22 '11 at 15:35

t-clausen.dk

43,517
12
59
92

How do i alter the code below to pad the minute and seconds with a 0 when it is a single digit?

2 Answers2