cast in sql server 2005

i want to add 0 to a number

DECLARE  @AccPD Char(6)
DECLARE  @PostMT Char(2)

SELECT @accpd ='201102'

select @PostMT  = case when substring(@accpd,5,2) = '01'
  then substring(@accpd,5,2) + 11
  else CAST('00' AS Char(2))  + substring(@accpd,5,2)-1
 end 

SELECT @P开发者_Go百科ostMT 

this should return a value 01 instead of 1

DECLARE @Number INT

SET @Number = 9

Select '0' + CAST(@Number as VarChar(30))

Will output '09'

I would give more specific help but for the life of me I can't really understand what you're trying to accomplish here.

Additionally if you want to just pad a Int and return a string here's a function for that:

/*
   Usage: 
   select dbo.fn_PadInt(3, '0', 5)

   Returns '00003'
*/
CREATE FUNCTION fn_PadInt
(
    @Input Int,
    @PadCharacter Char(1),
    @Length Int
)
RETURNS VarChar(20)
AS
BEGIN

    DECLARE @InputLen INT 
    DECLARE @Output VarChar(20)

    SET @InputLen = LEN(@Input)
    SET @Output = CAST(@Input as VarChar(20))

    WHILE @InputLen < @Length
       BEGIN
           SET @Output = @PadCharacter + @Output
           SET @InputLen = @InputLen + 1
       END

    Return @Output

END

DECLARE  @AccPD Char(6)
DECLARE  @PostMT Char(2)

SELECT @accpd ='201102'

select @PostMT  = case when substring(@accpd,5,2) = '01'
  then right(substring(@accpd,5,2) + 11, 2)
  else right(100 + substring(@accpd,5,2)-1, 2)
 end 

SELECT @PostMT

The problem was that when you use

  then substring(@accpd,5,2) + 11

in the first branch of a CASE statement, the +11 produces an INT result, which also forces the 2nd branch to become an INT result (of 1). When the [INT] result is then assigned to the variable @PostMT Char(2), it is at that point that the INT (1) is converted to "1 " (note the space due to fixed-width char2)

Padding left is usually done with REPLICATE

DECLARE  @AccPD Char(6)
DECLARE  @PostMT Char(2)

SELECT @accpd ='201102'

select @PostMT  = case when substring(@accpd,5,2) = '01'
  then substring(@accpd,5,2) + 11
  else  substring(@accpd,5,2)-1
 end 


SET @PostMT =  REPLICATE('0', 2 - LEN(@PostMT)) + @PostMT
SELECT @PostMT