T-SQL Format integer to 2-digit string

Question

I can't find a simple way to do this in T-SQL.

I have for example a column (SortExport_CSV) that returns an integer '2' thru 90. If the stored number is a single digit, I need it to convert to a 2 digit string that begins with a 0. I have tried to use CAST but I get stuck on how to display the style in the preferred format (0#)

Of course it is easy to do this on the front end (SSRS, MSAccess, Excel, etc) but in this instance I have no front end and must supply the raw dataset with the already formatted 2 digit string.

Answer 1

select right ('00'+ltrim(str( <number> )),2 ) 

Answer 2

You can use T-SQL's built in format function:

declare @number int  = 1 select format (@number, '0#')