Solved

SQL Query to update Expiration Date on CC

Posted on 2008-10-22
44
474 Views
Last Modified: 2008-12-06
Hello experts.
I have a DB that has a table with CCExpDate as credit card expiration. It is currently in format MMYY and data type is varchar. I would like to be able to update these dates to reflect any expired dates as of today are updated where Field (CCNo = 4 or 5) ADD + 2 years and where (CCNo= 3) ADD + 3 years.
I should be able to run this whenever necessary.

This is a question that has been reopened because we are still having issues with it.
The field in question is varchar in format as such '1206'

I have listed the latest from another contributor.

When running the above query.. it captures Oct, Nov and Dec.. but... it updates them beyond where they should. For instance, I ran it on 1106 and it updated to 1110. In fact.. it should only update to 1108 because that would still be valid. Did same thing for 1206.. it updated to 1210 when it shouldve updated to 1208.
Any ideas?

Thanks for your help
UPDATE MyTable

SET CCExpDate  = RIGHT('00' + Left(CCExpDate,2),2) +

     Right('00' + Cast((Cast(Right(year(getdate()),2) as smallint) + 2) As Varchar),2)

WHERE (Cast(Left(CCExpDate,2) as smallint) <= (Month(GetDate())-1)

AND Cast(Right(CCExpDate,2) as smallint) <= Cast(Right(Year(GetDate()),2) as Smallint) 

OR Cast(Right(CCExpDate,2) as smallint) < Cast(Right(Year(GetDate()),2) as Smallint) )

AND CCNo like '387845635%'

Open in new window

0
Comment
Question by:intelecorp
  • 22
  • 19
44 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22777824
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
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22777883
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.
0
 

Author Comment

by:intelecorp
ID: 22778406
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?
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22778498
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%'
0
 

Author Comment

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

0
 
LVL 16

Expert Comment

by:brad2575
ID: 22780054
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%'
0
 

Author Comment

by:intelecorp
ID: 22780154
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?
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22780302
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))
0
 

Author Comment

by:intelecorp
ID: 22780655
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
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22784775
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))
0
 

Author Comment

by:intelecorp
ID: 22787609
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
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22788085
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))
0
 

Author Comment

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

ODCALLCENTER.dbo.Call_Request
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22789600
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))
0
 

Author Comment

by:intelecorp
ID: 22790437
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
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22795614
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
0
 

Author Comment

by:intelecorp
ID: 22821650
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.

0
 
LVL 16

Expert Comment

by:brad2575
ID: 22831080
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

0
 

Author Comment

by:intelecorp
ID: 22833255
Hi Brad
Same error as above
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22833366
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.

0
 

Author Comment

by:intelecorp
ID: 22833478
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
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 16

Expert Comment

by:brad2575
ID: 22833642
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
0
 

Author Comment

by:intelecorp
ID: 22833831
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'm' to data type int.
Thats the new message
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22834513
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)
0
 

Author Comment

by:intelecorp
ID: 22835283
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?

0
 
LVL 16

Expert Comment

by:brad2575
ID: 22839683
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)
0
 

Author Comment

by:intelecorp
ID: 22841838
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?

0
 
LVL 16

Expert Comment

by:brad2575
ID: 22842335
-- 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.
0
 

Author Comment

by:intelecorp
ID: 22842563
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?
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22842790
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))
)
0
 

Author Comment

by:intelecorp
ID: 22850818
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.

0
 
LVL 16

Expert Comment

by:brad2575
ID: 22851154
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))
)
0
 

Author Comment

by:intelecorp
ID: 22851441
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.
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22851467
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.
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22851504
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))
)
0
 

Author Comment

by:intelecorp
ID: 22851613
I tried above.. same result.
How can I determine which record it died on first?
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22851979
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))
)
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22851986
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.
0
 

Author Comment

by:intelecorp
ID: 22852070
Hi Brad,
I dont want to run an update until Im sure it works
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22852561
no problem did you try my latest query above?
0
 

Author Comment

by:intelecorp
ID: 22852819
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?
0
 
LVL 16

Accepted Solution

by:
brad2575 earned 500 total points
ID: 22852860
Sorry, if I do not know what/where the bad data is, it is really hard to determine the problem, especially if it is working for some of the records.

This should be the update statement:

UPDATE ODCALLCENTER.dbo.Call_Request
Set CCExpDate =
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')
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))
)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now