Replace last two characters in column

Question

I have a column with dates in the format 201201, 201202, 201203 etc.

This is a financial database so there is a period 13; however periods 12 & 13 are combined for reporting purposes.

When the last two characters are 13, how do I replace them with 12?

I started off with

SELECT REPLACE(PERIOD, '13', '12')
FROM @b_s_summary

but, of course this messes with the year when it's 2013.

All advice gratefully received.

Answer 1

You can use Substring in this case:

SELECT REPLACE(PERIOD, SUBSTRING(PERIOD, len(PERIOD) - 1, 2), '12')
FROM @b_s_summary WHERE PERIOD LIKE '%13'

Does that fit you, or do you need a more dynamic approach for past or upcoming years?

Answer 2

declare @Periods as Table ( Period VarChar(6) )
insert into @Periods ( Period ) values
  ( '201212' ), ( '201213' ), ( '201312' ), ( '201313' ) 

select Period, case
  when Right( Period, 2 ) = '13' then Substring( Period, 1, 4 ) + '12'
  else Period end as [AdjustedPeriod]
  from @Period