Compare Month & Year to current date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Compare Month & Year to current date

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2006-04-10 : 06:36:15

I need to write a Stored Proc that will validate the expiry date of a users Credit Card. I only have the Month and Year to work with though. These are stored in 2 columns.

Basically, the date retrieved from the Month and Year columns must be checked against today's date and send a return value signifying Pass or Fail. The bit I am having a problem with is only having Month and Year to work with.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-10 : 07:19:53

well you have atleast the luxury of having 2 columns separating the month and year...

it's pretty straightforward...

not sure of your approach but check out the following functions:
month, year, getdate

--------------------
keeping it simple...

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-10 : 08:23:01

[code]
declare @m int, @y int, @sd varchar(20), @d datetime, @ed datetime
Set @m = 4
Set @y = 2006
Set @sd = convert(varchar(2), @m) + '/01/' + convert(varchar(4), @y)
Print @sd
Set @d = Convert(datetime, convert(varchar(2), @m) + '/01/' + convert(varchar(4), @y))
Print @d
Set @ed = DATEADD(d,-1,DATEADD ( m , 1, @d ) )
print @ed

if @ed < getdate()
print 'Expired'
else
print 'Valid'
[/code]
Srinika

Kristen
Test

22859 Posts

Posted - 2006-04-10 : 08:44:40

Something like:


IF @CreditCardYear > DATEPART(YEAR, GetDate())
   OR (@CreditCardYear = DATEPART(YEAR, GetDate())
       AND @CreditCardMonth > DATEPART(MONTH, GetDate()))
BEGIN
    ... Expiry Date is OK ...
END
ELSE
BEGIN
    ... Expiry Date is Duff ...
END

Note: @CreditCardYear needs to include the century (add 2000 if its a 2 digit year)

"Set @sd = convert(varchar(2), @m) + '/01/' + convert(varchar(4), @y)"

Using "yyyymmdd" would prevent a country-specific date conversion!

Kristen

patshaw
Posting Yak Master

177 Posts

Posted - 2006-04-10 : 10:08:51

Thanks Guys, never occured to me to use DatePart. Works great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources