How do I get back a 2 digit representation of a number in SQL 2000

Question

I have a table on SQL2000 with a numeric column and I need the select to return a 01, 02, 03...

It currently returns 1,2,3,...10,11...

Thanks.

Answer 1

Does this work?

SELECT REPLACE(STR(mycolumn, 2), ' ', '0')

From http://foxtricks.blogspot.com/2007/07/zero-padding-numeric-value-in-transact.html

Answer 2

John's answer works and is generalizable to any number of digits, but I would be more comfortable with

select case when mycolumn between -9 and 9 then '0' + str(mycolumn) else str(mycolumn) end

Answer 3

where n is a positive integer between 0 and 99:

select right('0'+ltrim(str(n)),2)

or

select right(str(100+n),2)

but I like John's answer best. Single point of specification for target width, but I posted these because they are also common idioms that might work better in other situations or languages.

Answer 4

This sort of question is about the interface to the database. Really the database should return the data and your application can reformat it if it wants the data in a particular format. You shouldn't do this in the database, but out in the presentation layer.