CODEWITHSUNDEEP
sqlsql-server
sergio089
sergio089

Reputation: 161

SQL get records that expire in 7 days

I hope someone can point out what I'm doing wrong because I have no idea. It's my first time using the DATEADD. I tried putting +7,+8 just in case but nothing. I have no idea what I'm doing wrong. I only need the records from 7 days, not between those days.

SELECT  A.ID
       ,B.EMAIL
       ,A.NUMBER 
       ,A.EXPIRATION

  FROM vw_RECORD AS A
  LEFT JOIN vw_INFO AS B ON A.ID=B.ID
  WHERE NUMBER LIKE 'C%'
  AND A.EXPIRATION = DATEADD(DAY,7,GETDATE())

The result without the last AND statement gets me:

ID          EMAIL           NUMBER         EXPIRATION
530c    [email protected]     C-0001    2017-08-29 00:00:00.000

Thank you in advanced.

Upvotes: 1

Views: 2092

Answers (3)

Niraj Rajpurohit
Niraj Rajpurohit

Reputation: 34

Try This : 

  SELECT  A.ID
           ,B.EMAIL
           ,A.NUMBER 
           ,A.EXPIRATION
      FROM vw_RECORD AS A
      LEFT JOIN vw_INFO AS B ON A.ID=B.ID
      WHERE NUMBER LIKE 'C%'
      AND DATEDIFF(DAY,A.EXPIRATION,GETDATE())=7

Upvotes: 0

MirzaS
MirzaS

Reputation: 512

My guess is that DATEADD can't accept DAY as the first argument, since it uses abbrevations (e.g mm for month), so the code would look like this :

SELECT  A.ID
       ,B.EMAIL
       ,A.NUMBER 
       ,A.EXPIRATION

  FROM vw_RECORD AS A
  LEFT JOIN vw_INFO AS B ON A.ID=B.ID
  WHERE NUMBER LIKE 'C%'
  AND A.EXPIRATION = DATEADD(dd,7,GETDATE())

Also as getdate adds time to the end and I'm guessing expiration also has time part since you probably just added 7 days to the current date, it'll never be the exact same. You could try converting both without a time part like this :

SELECT  A.ID
       ,B.EMAIL
       ,A.NUMBER 
       ,A.EXPIRATION

  FROM vw_RECORD AS A
  LEFT JOIN vw_INFO AS B ON A.ID=B.ID
  WHERE NUMBER LIKE 'C%'
  AND CONVERT(date, A.EXPIRATION) = CONVERT(date, DATEADD(dd,7,GETDATE()))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

In SQL Server, GETDATE() (despite its name) has a time component. So, you need to remove it:

A.EXPIRATION = CONVERT(DATE, DATEADD(DAY, 7, GETDATE()))

Upvotes: 4

Related Questions