Search code examples
sql-servervarchartypeconverter

SQL Server CONVERT int to varchar with padding

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"?

Solution

  • Wrap it in:

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

    Alternatively:

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