SQL Query to update Expiration Date on CC

Hello intelecorp,

I would suggest that you are using the wrong data type.  Use datetime (or if you prefer, smalldatetime), and
now all of your updates become *much* easier.

Regards,

Patrick

Im not sure what all the casting in the where clause is.

Since you do not need to change the month and only the year this should work fine:

UPDATE MyTable
SET CCExpDate  =
Left(CCExpDate,2) + Cast(Cast(Right(CCExpDate,2) as int) +
      Case When CCNo IN (4, 5) Then 2 Else 3 END
as varchar(20)) END
WHERE CCNo like '387845635%'


as long as the format is always MMYY.

Hi Brad,

The format is always MMYY. Some of the data is corrupted in this field with a 06YY format.
the expiration should be relative to todays date.. so for instance... 1008 is still valid but 0908 isnt and would have to be update (if card begins wtih 4 or 5) to 0910. Similarly.. if card Exp is 0906 then it would have to change to 0910 since adding 2 years would still result in an expired value. Same goes for card numbers beginning with 3. Here it should add 3 yrs.
Is that the exact query? Im trying to run it against test cards where CCNo like '37834840451%' and CCExpDate = '1206'
When I run above.. it doesnt like the Where clause?
Any suggestions?

here you go.  the where was my fault, I had an extra "END" in my statement.

I updated the query as well so it will update ALL the records in the database if the month <= current MONTH AND year <= current year.

If the CCNo = 4 or 5 it will add 2 else adds 3 years to the year.

This does not account for the bad data you described above.  

Did you need this script to account for that?  Or do you want one to just fix it so you only run it once?



Select Left(CCExpDate,2) + Cast(Cast(Right(Year(GetDate()) ,2) as int) +
      Case When CCNo IN (4, 5) Then 2 Else 3 END
as varchar(20))
WHERE Left(CCExpDate, 2) <= Month(GetDate()) AND Right(CCExpDate, 2) <= Right(Year(GetDate()), 2)
AND CCNo like '387845635%'

Hi Brad,
Is this a Select or an update?
I would like it so that it ignores the bad records for now

sorry was testing with the select, here is the update.  It does ignore the bad data currently.

UPDATE MyTable
SET CCExpDate  = Left(CCExpDate,2) + Cast(Cast(Right(Year(GetDate()) ,2) as int) +
      Case When CCNo IN (4, 5) Then 2 Else 3 END
as varchar(20))
WHERE Left(CCExpDate, 2) <= Month(GetDate()) AND Right(CCExpDate, 2) <= Right(Year(GetDate()), 2)
AND CCNo like '387845635%'

Hi Brad.. tried that query
The test record was to be WHERE CCNo='37834840451%'
The CCExpDate value for this record is '1206'
Yet the query returns ZERO records updated?
Any ideas?

k, found the issue (sorry).  try this:

UPDATE MyTable
SET CCExpDate  =  Left(CCExpDate,2) + Cast(Cast(Right(Year(GetDate()) ,2) as int) +
      Case When CCNo IN (4, 5) Then 2 Else 3 END
as varchar(20))
WHERE (Left(CCExpDate, 2) <= Month(GetDate()) AND Right(CCExpDate, 2) = Right(Year(GetDate()), 2))
OR (Right(CCExpDate, 2) < Right(Year(GetDate()), 2))

Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '111111111111111' overflowed an int column. Maximum integer value exceeded.
The statement has been terminated.

HI Brad.. Thats the message I got when running it

im not even sure how that number is getting generated as it should only be generating a 4 character number

Try this:
UPDATE MyTable
SET CCExpDate  =  Left(CCExpDate,2) + Cast(Cast(Right(Year(GetDate()) ,2) as int) +
      Case When CCNo IN (4, 5) Then 2 Else 3 END
as varchar(4))
WHERE (Left(CCExpDate, 2) <= Month(GetDate()) AND Right(CCExpDate, 2) = Right(Year(GetDate()), 2))
OR (Right(CCExpDate, 2) < Right(Year(GetDate()), 2))

Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '111111111111111' overflowed an int column. Maximum integer value exceeded.
The statement has been terminated.

Same message

can you run this to see what results you get and see if you get all the 1's as a result

select Left(CCExpDate,2) + Cast(Cast(Right(Year(GetDate()) ,2) as int) +
      Case When CCNo IN (4, 5) Then 2 Else 3 END
as varchar(4))
WHERE (Left(CCExpDate, 2) <= Month(GetDate()) AND Right(CCExpDate, 2) = Right(Year(GetDate()), 2))
OR (Right(CCExpDate, 2) < Right(Year(GetDate()), 2))

HI Brad.. How do I run this query if the Database Name and table is as follows:

ODCALLCENTER.dbo.Call_Request

select Left(CCExpDate,2) + Cast(Cast(Right(Year(GetDate()) ,2) as int) +
      Case When CCNo IN (4, 5) Then 2 Else 3 END
as varchar(4))
From ODCALLCENTER.dbo.Call_Request
WHERE (Left(CCExpDate, 2) <= Month(GetDate()) AND Right(CCExpDate, 2) = Right(Year(GetDate()), 2))
OR (Right(CCExpDate, 2) < Right(Year(GetDate()), 2))

Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '008001006119' overflowed an int column. Maximum integer value exceeded.
Thats the message

Im sorry I do not know what is wrong then.  It worked fine in all my tests.  The only thing I can think of is some bad data or something that I can not account for in the script that is causing this.  

My code should work fine.

Try just selecting this:
without the where clause to see if the where clause is causing the issue
select Left(CCExpDate,2) + Cast(Cast(Right(Year(GetDate()) ,2) as int) +
      Case When CCNo IN (4, 5) Then 2 Else 3 END
as varchar(4))
From ODCALLCENTER.dbo.Call_Request

Brad,
Looking at this query, I think it is attempting to convert my field CCNo as varchar 4 resulting in the overflow. I think CCExpDate (which is 4 varchar) needs to be converted.
As I run this query, I get
Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '4520010012570000' overflowed an int column. Maximum integer value exceeded.

ok try this one, this should not be selecting any amount of characters above 4 digits (2 for each part).

select
cast(Left(CCExpDate,2) as varchar(2)) +
Cast(
      Cast(Right(Year(GetDate()) ,2) as int) +
      Cast(Case When CCNo IN (4, 5) Then 2 Else 3 END as int)
as varchar(2))
From ODCALLCENTER.dbo.Call_Request

Hi Brad
Same error as above

is the ccexpdate always 4 characters long?  if the month is september/9 is it always "09"?

also just noticed (for some reason didnt see it before) in your original post you said you wanted to do this check for adding 2 or 3 years:
   CCNo = 4 or 5...

but CCNo in other posts is the full credit card number.  What/how/why are you checking if CCNo = 4 or 5?  Are you checking for the first character/digit or last digit of CCNo = 4 or 5?  and NOT the whole thing?  If so that is the problem, let me know if so and I can update the query.

If not then the 4 or 5 check needs to be on a different field then CCNo.

HI Brad,

Basically.. the query needs to check the 1st digit of the CCNo.. If it is a 4 or 5, then CCExpDate YY needs to be updated to +2 years at a time until current. If CCNo 1st digit begins with a 3, then +3 years need to be added.
Hope that helps

yes that is where the problem is occuring (sorry I did not notice it before).

Try this for the select part of the query:

select
cast(Left(CCExpDate,2) as varchar(2)) +
Cast(
      Cast(Right(Year(GetDate()) ,2) as int) +
      Cast(Case When Left(CCNo, 1) IN (4, 5) Then 2 Else 3 END as int)
as varchar(2))
From ODCALLCENTER.dbo.Call_Request

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'm' to data type int.
Thats the new message

looks like the bad data you mentioned is now causing errors (because of the updates, it wasnt before on my initial tests).  Try this:

select
cast(Left(CCExpDate,2) as varchar(2)) +
Cast(
      Cast(Right(Year(GetDate()) ,2) as int) +
      Cast(Case When Left(CCNo, 1) IN (4, 5) Then 2 Else 3 END as int)
as varchar(2))
From ODCALLCENTER.dbo.Call_Request
where (ISNUMERIC(Left(CCNo, 1)) = 1)
AND (ISNUMERIC(Left(CCExpDate,2)) = 1)

Hey Brad,
Ok.. looks like we might be getting somewhere.. I see a column with MMYY... ie (1210)
Im assuming its showing me updated CCExpDate Value.
I tried on a sample CCNo like '3783484045%'
But the values returned were 1211. The original value was 1206. The correct value displayed shouldve been 1209. The Card starts with 3 so it should add 3 years to 1206 making it 1209. Since 1206 expired, it should reflect value of 1209 which would be considered valid relative to today's date?
ANy idea?

Yes I thought in one of the above threads that you said you wanted the 2 or 3 years added to the CURRENT date so 3 + 2008 = 2011.  That is how the code is set up is to add the 2 or 3 to the current year, not the year on the card.  I had set it up to add the 2 or 3 to the year on the card but you stated this would not work?

If you need it to add to the existing card value you can do this instead:
select
cast(Left(CCExpDate,2) as varchar(2)) +
Cast(
      Cast(Right(Year(CCExpDate) ,2) as int) +
      Cast(Case When Left(CCNo, 1) IN (4, 5) Then 2 Else 3 END as int)
as varchar(2))
From ODCALLCENTER.dbo.Call_Request
where (ISNUMERIC(Left(CCNo, 1)) = 1)
AND (ISNUMERIC(Left(CCExpDate,2)) = 1)

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Thats the message.
One thing.. I need to make sure that the CCExpDate is only updated if it is expired. To check.. when query is run.. it will check existing values against today's date.. then update those... so everytime query is run on specific set of data it will update accordingly.
In addition, in come cases... the card will have to add 2 or 3.. but even after adding that.. the date will still be expired.. so it will have to add another 2 or 3? ie... 1205.. would update to 1207 which would still be expired relative to today.. so it would have to update to 1209.

I hope that clarifies things?

-- this will fix the issue you have above AND will only select ones that have the expiration date as expired.

select
cast(Left(CCExpDate,2) as varchar(2)) +
Cast(
      Cast(Right(CCExpDate ,2) as int) +
      Cast(Case When Left(CCNo, 1) IN (4, 5) Then 2 Else 3 END as int)
as varchar(2))
From ODCALLCENTER.dbo.Call_Request
where (ISNUMERIC(Left(CCNo, 1)) = 1)
AND (ISNUMERIC(Left(CCExpDate,2)) = 1)
AND (
    (Left(CCExpDate, 2) <= Month(GetDate() AND Right(CCExpDate, 2) = Right(Year(GetDate()), 2))
    OR
    (Right(CCExpDate, 2) < Right(Year(GetDate()), 2))
)



For the adding 2 or 3 twice:
1.  Will it only need to do this twice at MOST?  So it will only need to add 2 or 3 once, then its still expired so add 2 or 3 one more time (AND THAT IS IT)?  Or can this need to happen more then twice.  (i.e. the current expire date is 0901?  so you would have to add 2 up to 4 times at least, can this happen at all)?

2.  Is this even needed in the script itself?  Or can you just run this multiple times?

Let me know and I can figure out a solution depending on answers to 1 and 2.

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'AND'.

I get this error

With regards to your other question, I suppose I can have it run multiple times so long as those cards once updated and are no longer expired dont get converted again?

correct the additions to the where clause will make it so it only updates expired cards.

updated query (left off a paren)

select
cast(Left(CCExpDate,2) as varchar(2)) +
Cast(
      Cast(Right(CCExpDate ,2) as int) +
      Cast(Case When Left(CCNo, 1) IN (4, 5) Then 2 Else 3 END as int)
as varchar(2))
From ODCALLCENTER.dbo.Call_Request
where (ISNUMERIC(Left(CCNo, 1)) = 1)
AND (ISNUMERIC(Left(CCExpDate,2)) = 1)
AND (
    (Left(CCExpDate, 2) <= Month(GetDate()) AND Right(CCExpDate, 2) = Right(Year(GetDate()), 2))
    OR
    (Right(CCExpDate, 2) < Right(Year(GetDate()), 2))
)

Hey BRad,
Ran this query above

On a sample card.. that was originally 1206 (CCNo starts with a 3) so the correct value should be 1209
The value returned is 129. See the same problem across a spectrum of values.

ok I see the problem, it is loosing the leading "0" if the year is less then 10, try this:

select
cast(Left(CCExpDate,2) as varchar(2)) +
RIGHT('0' +
    Cast(
        Cast(Right(CCExpDate ,2) as int) +
        Cast(Case When Left(CCNo, 1) IN (4, 5) Then 2 Else 3 END as int)
    as varchar(2))
, '2')
From ODCALLCENTER.dbo.Call_Request
where (ISNUMERIC(Left(CCNo, 1)) = 1)
AND (ISNUMERIC(Left(CCExpDate,2)) = 1)
AND (
    (Left(CCExpDate, 2) <= Month(GetDate()) AND Right(CCExpDate, 2) = Right(Year(GetDate()), 2))
    OR
    (Right(CCExpDate, 2) < Right(Year(GetDate()), 2))
)

Hey Brad.. I think we are getting close.
It runs.. I can see some results. then get this error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '
' to data type int.

it sounds like it may be from other bad data somewhere.

Do you know what record this died on?  If so can you post the values of CCExpDate and CCNo for that record?

I will continue to look at this while you try to find that.

try this:

select
cast(Left(CCExpDate,2) as varchar(2)) +
RIGHT('0' +
    Cast(
        Cast(Right(CCExpDate ,2) as int) +
        Case When Left(CCNo, 1) IN ('4', '5') Then 2 Else 3 END            
    as varchar(2))
, '2')
From ODCALLCENTER.dbo.Call_Request
where (ISNUMERIC(Left(CCNo, 1)) = 1)
AND (ISNUMERIC(Left(CCExpDate,2)) = 1)
AND (
    (Left(CCExpDate, 2) <= Month(GetDate()) AND Right(CCExpDate, 2) = Right(Year(GetDate()), 2))
    OR
    (Right(CCExpDate, 2) < Right(Year(GetDate()), 2))
)

I tried above.. same result.
How can I determine which record it died on first?

try this, it makes sure the other part is a number before it does any updates

select
cast(Left(CCExpDate,2) as varchar(2)) +
RIGHT('0' +
    Cast(
        Cast(Right(CCExpDate ,2) as int) +
        Case When Left(CCNo, 1) IN ('4', '5') Then 2 Else 3 END            
    as varchar(2))
, '2')
From ODCALLCENTER.dbo.Call_Request
where (ISNUMERIC(Left(CCNo, 1)) = 1)
AND (ISNUMERIC(Left(CCExpDate,2)) = 1)
AND (ISNUMERIC(Right(CCExpDate,2)) = 1)
AND (
    (Left(CCExpDate, 2) <= Month(GetDate()) AND Right(CCExpDate, 2) = Right(Year(GetDate()), 2))
    OR
    (Right(CCExpDate, 2) < Right(Year(GetDate()), 2))
)

as for finding which field it errored out on, if you were running an update?  You could just run the select and find the first record that shows up and it should be that one.

If you were just doing a select, it would be hard to tell.

Hi Brad,
I dont want to run an update until Im sure it works

no problem did you try my latest query above?

Brad.. Is there a way I can omit the data that it cant convert? And perhaps spit that information out.. so it can be cleaned?
I think If I run this on a specific date range.. seems to be OK
What would the UPDATE query be exactly?