Concatenate with a smallint variable

• Thinky Night

  SSCommitted

  Points: 1663

  More actions

  March 6, 2013 at 12:18 am

  #269287

  Hi All,

  I could not format a column in the below,

  declare @table table (mMonth smallint);

  insert into @table values (1),(9),(11),(12);

  select mMonth,

  LEN(mMonth) Length,

  case LEN(mMonth) when 1 then ('0'+CAST(mMonth AS VARCHAR(6))) else mMonth end formattedMMonth

  from @table

  case LEN(mMonth) when 1 then ('0'+CAST(mMonth AS VARCHAR(6))) else mMonth end formattedMMonth

  keyword could not give the correct result.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 6, 2013 at 2:25 am

  #1593830

  Try this:

  RIGHT('0'+CAST(mMonth AS VARCHAR(2)),2)

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Thinky Night

  SSCommitted

  Points: 1663

  More actions

  March 6, 2013 at 2:48 am

  #1593833

  It does not work. 🙁

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 6, 2013 at 2:56 am

  #1593835

  Thinky Night (3/6/2013)

  ---

  It does not work. 🙁

  What results do you get, and what results are you expecting?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Thinky Night

  SSCommitted

  Points: 1663

  More actions

  March 6, 2013 at 4:03 am

  #1593855

  I got

  111

  919

  11211

  12212

  I expect

  1101

  9109

  11211

  12212

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 6, 2013 at 4:06 am

  #1593858

  Thinky Night (3/6/2013)

  ---

  I got

  111

  919

  11211

  12212

  I expect

  1101

  9109

  11211

  12212

  It works for me:

  declare @table table (mMonth smallint);

  insert into @table values (1),(9),(11),(12);

  SELECT

  mMonth,

  LEN(mMonth) Length,

  case LEN(mMonth) when 1 then ('0'+CAST(mMonth AS VARCHAR(6))) else mMonth end formattedMMonth,

  RIGHT('0'+CAST(mMonth AS VARCHAR(2)),2)

  FROM @table

  mMonthLengthformattedMMonth(No column name)

  11101

  91909

  1121111

  1221212

  Copy-paste error?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden