SQL Server CONVERT int to varchar with padding

Question

Possible Duplicate:
Most efficient T-SQL way to pad a varchar on the left to a certain length?

I am returning the numeric value of the week given a datetime by using the datepart function in SQL. I then convert this to a varchar in order to build a string. My problem is that I need weeks that have a value less than 10 to become "01" instead of "1". I need this because it is stored that way in another database.

My question is, is there some kind of specification I can give to the CONVERT function in order to force the varchar to be of length 2 instead of length 1?

Here is the statement I have right now:

convert(varchar(2),datepart(ww,'2013-1-3 11:00:00.000'))

Can I make this return "01" instead of "1"?

See http://stackoverflow.com/questions/5540064/padleft-function-in-t-sql — David, Dec 27 '12 at 17:31
That did it. I used Martin Smith's response using the RIGHT function. Thanks — user912447, Dec 27 '12 at 17:40

score 7 · Accepted Answer · edited Dec 27 '12 at 17:53

7

Wrap it in:

right('0' + convert(...), 2)

Alternatively:

right(100 + datepart(ww, ...), 2)

edited Dec 27 '12 at 17:53

Andriy M

76,112
17
94
154

answered Dec 27 '12 at 17:30

Andomar

232,371
49
380
404

Yeah that works. You can also use RTRIM instead of CONVERT, you can just do: right('0'+rtrim(datepart(ww,'2013-4-20 11:00:00.000')), 2) – user912447 Dec 27 '12 at 17:41

SQL Server CONVERT int to varchar with padding

1 Answers1