Need dates that are less than 50 days away from now

I have this script that needs to pull records that are less than 50 days away form current date. This is a warning system about some cards that will expire in less than 50 days from current.

The script seems to be working for the most part but for some reason I am getting a few dates that are 2014-06-30.. That date is NOT less than 50 days away. I will post the code and the results.
create table ##tokens  (user34 nvarchar(200), DateDeath date)
 
declare @user34 nvarchar(200), @dateDeath nvarchar(200)
declare @sql nvarchar(2000)
declare @warningdate date
declare @expiresdate date
declare @serialNBR nvarchar(20)


--set the warning date variable. This says if its less than 50 days away
 set @warningdate = DATEADD(day,50,getdate())
 --SET @warningdate =   getdate() +50
SELECT @warningdate



INSERT  ##tokens (user34, DateDeath)
SELECT  m.NameReg,
        (SELECT TOP 1
                dbo.secureToken.[DateDeath]
        FROM    dbo.secureToken
        WHERE   usedBy34 = s.usedBy34
        ORDER BY dbo.secureToken.[DateDeath] DESC
                   ) DateDeath
FROM    AssetManagement.dbo.secureToken s
        INNER JOIN dbo.Users_Master m ON s.usedBy34 = m.user34
WHERE   s.DateDeath < @warningdate


select * from ##tokens



user34            DateDeath
Shawn Hess	2014-06-30
Caroline Foust	2010-05-31
Sarah Hanson	2010-05-31
Allison Jones	2010-05-31
Troy Baskin	2010-04-30
John Haeger	2010-05-31
Kerry Johnson	2010-04-30
Jennifer Bolkcom	2014-03-31
Patricia Barrow	2010-05-31
Toni Oosting	2010-05-31
Dana Leonard	2010-05-31
Tiffanie Markus	2010-05-31
Sheri Mersch	2010-05-31
Julia Love	2010-04-30
Brandy Martin	2010-05-31
Barbara Thomas	2010-04-30
Rita Hatfield	2014-03-31
Jason Williams	2010-05-31
Wendy Pinson	2010-05-31
Mythili Shastry	2013-05-31
Mange Li	2010-04-30
Anne Gary	2010-04-30
Marsha Baskette	2010-04-30
Julienne Martinson	2014-03-31
Stacie Thomas	2010-05-31

Open in new window

dboyd02Asked:
Who is Participating?
 
mdagisConnect With a Mentor Commented:
May try something like:

INSERT  ##tokens (user34, DateDeath)
Select NameReg, DateDeath
From
(
SELECT  m.NameReg,
        (SELECT TOP 1
                dbo.secureToken.[DateDeath]
        FROM    dbo.secureToken
        WHERE   usedBy34 = s.usedBy34
        ORDER BY dbo.secureToken.[DateDeath] DESC
                   ) DateDeath
FROM    AssetManagement.dbo.secureToken s
        INNER JOIN dbo.Users_Master m ON s.usedBy34 = m.user34
) DT
WHERE   DateDeath < @warningdate
0
 
halfbloodprinceCommented:
Try this:
Select
* From YourTableName
Where DateDiff(dd,YourDateColumn,getDate()) = 50
0
 
mdagisCommented:
The field that you check "s.DateDeath < @warningdate" is NOT the same with the one that you insert in the temporary table. The one that you insert is the top 1 which means the first record
0
 
dboyd02Author Commented:
halfboddprince: this returned no records I tried < as well. It returned 198 records where I know there are about 22 that are expiring. Thanks though.

Mdagis. I am checking into yours now.
0
 
dboyd02Author Commented:
mdaqis:
That worked perfectly!!!!!! Thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.