Well, I'm going nuts.

It just struck me that the answer to my overall problem is to do some string manipulation and then DATEADD, but I still don't know why I getting what SQL is giving me.

To get the prior month, I use the following code;

SELECT
  (
  SELECT
    RIGHT('00' + CAST(
      CASE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
        WHEN 0 THEN 12
        ELSE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
        END AS varchar(2)), 2)
  )--,  -- Month
  --CAST(CAST(LEFT(MAX(Eff_Period), 4) AS int) - 1 AS varchar(4))  -- Year
FROM
  Current_Membership;

This correctly gives me the proper month number for the prior month, in this case, '06' since the current month is '07.'

But, for debugging, I wanted to test to make sure it would properly adjust for January - '01' - by replacing MAX(Eff_Period) with '201501.'

It did. Worked fine. Cool!

Then, to make really sure, I put in '201502.'

Whoa!!

Instead of the single record of '01,' it shot out 177,209 rows of all '02's!

I put back in the MAX(Eff_Period) instead of the date string constant and, worked fine. Swapped back in the date string - boom! - 177,209 rows again.

Like I said, I can use DATEADD - which, I should have thought of earlier, being familiar with it - but, still, what the hey is going on? Any ideas?

Alright. Now I'm really confused. I changed the date string to '201501' just to verify before posting this, and it spit out 177,209 rows of '01's! So, to keep from altering things and to maintain the edit history, I started up another query and C&P'd everything to it. Then, on the original, I backed up to an earlier version, one that worked. That listing is the same as above, but the commented out comma in line 9 and the commented CAST in the line after it - to get the year - were uncommented.

Now, it works with both '201501' and '201502.' But, if I replace the comments, basically removing the CAST with the year, it slams back with 177,209 rows!

My brain is starting to hurt from banging it on my desk in frustration - any thoughts on just what is going on?

If you use MAX (or any other aggregate function like SUM or COUNT) and do not use either a GROUP BY or an OVER clause, you will get only one row for the whole table.  So when you do

SELECT
  (
  SELECT
    RIGHT('00' + CAST(
      CASE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
        WHEN 0 THEN 12
        ELSE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
        END AS varchar(2)), 2)
  )
FROM
  Current_Membership;

you will get back only one row.  Bu if you don't use an aggregate function and don't use a GROUP BY, then you get back one row for every row in your table that is not eliminated by a WHERE clause or an ON condition in a JOIN clause.  So

SELECT
  (
  SELECT
    RIGHT('00' + CAST(
      CASE CAST(RIGHT('201501', 2) AS int) - 1
        WHEN 0 THEN 12
        ELSE CAST(RIGHT('201501', 2) AS int) - 1
        END AS varchar(2)), 2)
  )
FROM
  Current_Membership;

will return one row for every row in your table.  A way to test what would happen if '201501' was the largest value in your table would be to run

SELECT
  (
  SELECT
    RIGHT('00' + CAST(
      CASE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
        WHEN 0 THEN 12
        ELSE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
        END AS varchar(2)), 2)
  )
FROM
  Current_Membership
WHERE Eff_Period <= '201501';

Tom

Make date math a thing of the past! Check out calendar tables!

I could not understand your problem as I tried your code as well but not getting the  ??? 177an other value

 declare @Eff_Period varchar(6)
  Set @Eff_Period= '201511'

   SELECT
    RIGHT('00' + CAST(
      CASE CAST(RIGHT(MAX(@Eff_Period), 2) AS int) - 1
        WHEN 0 THEN 12
        ELSE CAST(RIGHT(MAX(@Eff_Period), 2) AS int) - 1
        END AS varchar(2)), 2)

but the best way is to get previous/next or any value use date add and make a proper date format. Until and unless format is correct  SQL will not  throw error.
try this way
select   DATEADD(mm,-1,cast ('201502'+ '01' as datetime) ) as prevmonth

A useful idiom is a report period calendar. It gives a name to a range of dates. The worst way would be to use 

temporal math; it tells the world you do not think in sets or understand declarative programming yet. Here is a 
skeleton:  

CREATE TABLE Report_Periods
(report_name VARCHAR(30) NOT NULL PRIMARY KEY,
report_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
ordinal_period INTETGER NOT NULL UNIQUE 
  CHECK(ordinal_period > 0)
etc);

These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can 

be gaps between them; we have to wait a year between each AnnualGoing out Of BusinessSale! and there might be 

long stretches of time without any special sales. But we want to know their ranges so that the table is fairly 

constant once it is created.

I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within 

a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format 

required by Standard SQL.