CODEWITHSUNDEEP
sql
Sophtware
Sophtware

Reputation: 1834

Create a date from Credit Card expire in MMYY format

I need to convert a credit card expire field from MMYY to a date field I can use in a MS SQL query so I can compute when credit cards are expiring in the future. Basically, I need to go from MMYY to MM/DD/YYYY, where the day part could just be '01' (the first of the month).

I'm looking for credit cards that are expiring next month from a database. The problem I'm running into is when next month is the first month of the next year.

Here's the code I have for determining expired card:

(CAST(SUBSTRING(CCExpire,3,2) as int) + 2000 < YEAR(GETDATE()))
or
(
    (CAST(SUBSTRING(CCExpire,3,2) as int) + 2000 = YEAR(GETDATE()))
    AND
    (CAST(SUBSTRING(CCExpire,1,2) as int) < MONTH(GETDATE()))
)

And here's the code for cards expiring this month:

(CAST(SUBSTRING(CCExpire,3,2) as int) + 2000 = YEAR(GETDATE()))
AND
(CAST(SUBSTRING(CCExpire,1,2) as int) = MONTH(GETDATE()))

Now I need code for cards expiring next month...

Upvotes: 0

Views: 8131

Answers (4)

JohnB
JohnB

Reputation: 481

You can leverage the fact that a month always starts with day 01. Using this you can construct a valid date and parse it.

declare @exp nvarchar(4) = '0424'

select CONVERT(datetime, '01/' + SUBSTRING(@exp,1,2) + '/' + SUBSTRING(@exp,3,2), 3)

I've used the British dd/mm/yy format (3) for the date assembly and parsing, but you could use any that accept the 2 digit year, you'd just have to shuffle around your concatenation and use a different style number. This method handles 0398 -> 01/03/1998 and should be proof against the next century (you know they will find you!).

This will give you the start of the expiry month. With card expiry dates, you often want to know the end of the month. I.e. a card that expires 10/23 will actually expire on 31/10/2023 23:59:59. to get this you can use:

select DATEADD(second, -1, DATEADD(month, 1, CONVERT(datetime, '01/' + SUBSTRING(@exp,1,2) + '/' + SUBSTRING(@exp,3,2), 3)))

Which adds a month and takes away a second.

Upvotes: 0

jfollas
jfollas

Reputation: 1235

You're just looking for cards expiring in the next month? Why not just figure out the MMYY string of the month you're searching for, then use that in your predicate:

WHERE CCExpire = (RIGHT('00' + CAST(MONTH(DATEADD(mm, 1, GETDATE())) AS VARCHAR), 2) 
+ RIGHT(CAST(YEAR(DATEADD(mm, 1, GETDATE())) AS VARCHAR), 2))

Upvotes: 2

Tom Morgan
Tom Morgan

Reputation: 2365

Surely it's just

(CAST(SUBSTRING(CCExpire,3,2) as int) + 2000 = YEAR(GETDATE()))
AND
(CAST(SUBSTRING(CCExpire,1,2) as int) = MONTH(GETDATE())+1)

note the +1 after the MONTH(GETDATE())

ah - just noticed your "first month of year" problem. Hang on...

You can use:

CCExpire < DATEADD(mm,GETDATE(),1) AND CCExpire > GETDATE()

that way you don't have to worry about the problem of the month wrapping into next year.

Big Edit: only just realised that you don't have a date field - you have a MMDD field. So I reckon, build up the credit card date first, maybe into a parameter to make it easier to see whats going on, using DATEADD and then use that along with the code above to evaluate.

You can convert your MMYY dates using this:

print convert(datetime, str(2000+'10')+'06'+'01')

in this example 10 is the YY and 06 is the MM, so you could use something like:

print CONVERT(datetime, str(2000+SUBSTRING(CCExpire,3,2))+SUBSTRING(CCExpire,1,2)+'01')

Upvotes: 2

Marc B
Marc B

Reputation: 360772

Surely it'd be easier to convert the CC's MMYY format to SQL server's preferred date format client-side. That'd let you store it in a normal date field, from which you could then just do normal date math:

DATEADD(month, 1, GETDATE())

would properly return the equivalent date in the next month (and adjusts so that Jan 31 + 1 month = Feb 28)

Upvotes: 0

Related Questions