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 int开发者_如何学JAVAo 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...

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))

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')

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)