thought this might be helpful to someone else.
if you want to convert a numeric value e.g. 01 to a string 01 then this can be done like so:
select '0' + CAST(01 as varchar(2)) - results in 01
if you want to make this a little more generic so you can accomadate a conversion if required for number ranging 1-20 for example, then you could do this
select CAST(RIGHT('0'+ CAST(10 AS VARCHAR(2)),2) - this will result in 010, but using the right command we can strip this back to the two characters we need - 10.
an example of how I have used this is to construct a date from 3 fields:
OPENDATE - a datetime field holding just the date
OPENHOURS - the hour in which a task was completed for the given date
OPENMINUTES - the minute of the hour in which a task was completed for the given date.
the aim of the select statement is to convert
to a datetime field of '2008-10-27 09:53:00'
SELECT convert(VARCHAR(30), SUBSTRING(convert(varchar(20), OPENDATE, 120), 0, 11) + ' ' + CAST(RIGHT(' 0'+ CAST(OPENHOURS AS VARCHAR(2)),2) + ':'+ RIGHT(' 0'+ CAST(OPENMINUTES AS VARCHAR(2)),2) + ':00' AS VARCHAR(10))) AS [Open DT]
MCITP: Business Intelligence Developer (2005)