September 22, 2008 at 12:29 pm
We've been storing credit card expiration dates in varchar format as MMYY.
Well, before we would just create a new record and keep the old credit card number instead of deleting it (because in the same table we hold the address and it was a means of keeping customers previous addresses on file), but since we're switching to a new system we need to get rid of the old expired card numbers so we don't get charged for authorizations on cards that we KNOW are expired.
Lastly, we need this to work on a continual basis so when we upload an updated csv file to our processor, we *must* give them the date in the format of MMYY, any updates to what we're doing now need to factor that in... so when we pull that file, we're exporting directly from Enterprise Manager into a text file(csv) ready to import into their system.
So, my question is... how can we...
A. Leave the dates in varchar format and find out what cards expired and delete those records?
OR
B. Convert the varchar dates into datetime dates and delete the old card records without converting to a full date?
P.S. I'd like to add that some dates in these text fields aren't 4 digits. For example: 09/10 is in the database as 910.
* * * * Structure * * * *
==CardNum=|=CardExp==|====BillingAddress====
====int===|varchar(8)|====varchar (40)====
4444****** | 06/09 |123 Bobbaganoosh Lane
Any help would be appreciated!
September 22, 2008 at 1:16 pm
moving forward I'd no longer keep it in a varchar(4). I'd either use 2 char(2) columns one for month and 1 for year. Additionally I'd make sure the front end does some verification to add the missing 0's is the users don't. That will make your like much, much easier. You won't have to come up with logic that will try to distinguish 91/0 from 9/10. It's a piece of cake then to concat them together before sending them off to your verification provider.
Also, in the process of saving your company some cash, I'd think it wouldn't be hard to do some limited verification of the card numbers themselves prior to posting to your verification provider (possibly help you to reduce the number of bad numbers you get charged for.) http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=8172&lngWId=4
As far as finding the expired ones... You should theoretically always have the last 2 digits being the year so even in the case of 910, 2010 would be the year.
CREATE TABLE #TEST(
EXPDate VARCHAR(4),
CardID INT IDENTITY)
INSERT INTO #test
SELECT '0910'
UNION ALL
SELECT '910'
UNION ALL
SELECT '808'
UNION ALL
SELECT '1008'
UNION ALL
SELECT '101'
--Separate out the Month and Year
SELECT
CASE WHEN LEN([EXPDate]) = 4 THEN LEFT([EXPDate], 2)
WHEN LEN(Expdate)< 4 THEN LEFT(ExpDate,1)
END AS MONTH,
Right([EXPDate], 2) AS YEAR,
CardID,
0 AS Old
INTO #ExpDates
FROM #test
--Finding the Expired Cards
UPDATE [#ExpDates]
SET OLD = 1
WHERE [YEAR] < RIGHT(YEAR(GETDATE()),2)
OR ([YEAR] = RIGHT(YEAR(GETDATE()),2)
AND [MONTH] < MONTH(GETDATE()))
SELECT *
FROM #ExpDates
--Cleanup
DROP TABLE #test
DROP TABLE #ExpDates
I'm sure there's a more elegant solution to this, but it should get you pointed in the right direction.
BTW, I'm not going to even get into the issues that storing CC numbers any longer than you actually need them gives rise to, because I assume that you are complying with all Fed, State and international laws/standards on the subject already. You may want to check with appropriate lawyers, auditors etc just in case, because I'm not one.
-Luke.
September 22, 2008 at 1:19 pm
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply