Change Last 2 Numbers in Case When-CodePudding

Trying to create a new column "FiscalQrtr" that uses "Date" column in a case when, but am unable to figure it out so I went the update approach and am receiving an error message that states

"Conversion failed when converting the varchar value '' to data type int".

The date column I believe is YYYYMM, so an example result would be: if Date = 200103 then FiscalQrtr = 200101

Update code looks like this:

select * 
into #claims1 
FROM #claims 

update #claims1
set Date = left(Date, len(Date)-2)   '01'
where Date in ('','','');

Any help would be greatly appreicated

CodePudding user response：

This is because the data type of your column Date is INT and an INT data type cannot be used with LIKE. So instead, convert the Column to VARCHAR and perform the update. Like this

update #claims1
set Date = left(CAST(Date AS VARCHAR(20)), len(CAST(Date AS VARCHAR(20))-2))   '01'
where RIGHT(CAST(Date AS VARCHAR(20)),2) in ('01','02','03');

CodePudding user response：

If the Date column is actually a 6 digit integer, representing YYYYMM, you can use a bit of arithmetic for this:

update #claims1
set Date = (Date / 100 * 100)   1
where Date % 100 <= 3;

(Date / 100 * 100) truncates it, then you just add 1

You can also do all four quarters at once

update #claims1
set Date = (Date / 100 * 100)   (Date % 100 / 4 * 3   1);

CodePudding user response：

Why not just do all 4 quarters at once?

UPDATE #claims1
SET Date = LEFT(Date, 4)   '0'   RTRIM((RIGHT(Date,2)-1)/3 1);

If you don't like the unintuitive -1/3 1 math, you can write something that is much clearer in intent:

UPDATE #claims1 SET Date = LEFT(Date, 4)   '0'  
  RTRIM(DATEPART(QUARTER, DATEFROMPARTS(LEFT(Date,4), RIGHT(Date,2), 1)));

If you just want to handle Q1 for some reason, ok, just add:

WHERE RIGHT(RTRIM(Date),1) < 4;

Example db<>fiddle