Concatenating two fields - can't get it to work

Transact-SQL
1

Hello, I have two fields [yr] and [period]........both are of type smallint. A typical value for [yr] is 2022 and a value for period can be anything from 1 to 12.

I'm trying to add a new column containing [yr] and [period] but I want to pad [period] with a leading ero if it's less than 10 - the desired output will always be a six character varchar like 202201, 202210, 202211 etc etc

I cannot get it to work using this, what am I doing wrong?

UPDATE [dbo].[fsm_Shared_Costs_Reporting]
SET YYYYMM = CASE WHEN period >= 10 THEN (yr & period) ELSE (yr & 0 & period) END

2

If YYYYMM is an int: yr*100 + [period]
If YYYYMM is char(6): CAST(yr AS CHAR(4)) + RIGHT('0' + CAST([period] AS varchar(2)), 2)

ps I do not think is a good idea to duplicate data like this. ie Either do the conversions at runtime or have a calculated column.

1 Like
3

Genius! Many thanks!

4 
 Select *
      , yyyymm = cast(yrs.yr * 100 + yrs.period As char(6))
   From (Values (2022, 1), (2022, 2), (2022, 3), (2022, 4), (2022, 5), (2022, 6)
              , (2022, 7), (2022, 8), (2022, 9), (2022, 10), (2022, 11), (2022, 12)) As yrs(yr, period);
1 Like
5

I'd use the method that @jeffw8713 used except I'd leave the answer as an INT. It'll take 2 fewer bytes of storage and memory and maybe a bit faster in searches.

I also agree with @Ifor that you might want to consider making a persisted computed column of that.

1 Like