Concatenate with a smallint variable

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Concatenate with a smallint variable

Concatenate with a smallint variable

Rate Topic

Display Mode

Topic Options

Author

Message

Thinky Night

Posted Wednesday, March 06, 2013 12:18 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:31 AM
Points: 123, Visits: 218

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.

Post #1427197

ChrisM@Work

Posted Wednesday, March 06, 2013 2:25 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 5,613, Visits: 10,981

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1427249

Thinky Night

Posted Wednesday, March 06, 2013 2:48 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:31 AM
Points: 123, Visits: 218

It does not work. Sad

Post #1427252

ChrisM@Work

Posted Wednesday, March 06, 2013 2:56 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 5,613, Visits: 10,981

Thinky Night (3/6/2013)

It does not work. Sad

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}

Post #1427255

Thinky Night

Posted Wednesday, March 06, 2013 4:03 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:31 AM
Points: 123, Visits: 218

I got

1 1 1
9 1 9
11 2 11
12 2 12

I expect
1 1 01
9 1 09
11 2 11
12 2 12

Post #1427282

ChrisM@Work

Posted Wednesday, March 06, 2013 4:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 5,613, Visits: 10,981

Thinky Night (3/6/2013)

I got

1 1 1
9 1 9
11 2 11
12 2 12

I expect
1 1 01
9 1 09
11 2 11
12 2 12

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

mMonth Length formattedMMonth (No column name)
1 1 1 01
9 1 9 09
11 2 11 11
12 2 12 12

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}

Post #1427285

« Prev Topic | Next Topic »

Permissions