469,275 Members | 1,459 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

Help needed in this DATEDIFF SQL Query

Hi Friends,

I have been trying to use this query to get all records that will expire within the next 30 days from the current date.

While I am getting records with the same query what is bothering is I'm also getting records for the days past by.

Expand|Select|Wrap|Line Numbers
  1. Select AccountId, ExpiryDate, DATEDIFF(DAY,GETDATE(),ExpiryDate) as DaysLeft From Usr_Accounts
  2. WHERE Status='Active'
  3. AND DATEDIFF(DAY,GETDATE(),ExpiryDate) < 31
  4.  
Expand|Select|Wrap|Line Numbers
  1.  
  2. Results I'm getting 
  3.  
  4. 000135910 2007-07-31 15:15:21.000 -141
  5. 000280196 2008-01-27 20:02:28.000 39
  6. 000482088 2050-01-01 00:00:00.000 15354
  7. 002100387 2007-08-10 23:59:59.000 -131
  8. 002101119 2007-08-10 23:59:59.000 -131
  9. 002101594 2007-08-10 23:59:59.000 -131
  10. 002101839 2007-08-10 23:59:59.000 -131
  11. 002103017 2007-08-10 23:59:59.000 -131
  12. 002107518 2007-08-10 23:59:59.000 -131
  13. 002107582 2007-08-10 23:59:59.000 -131
  14.  
  15.  
While I require record No. 2 & 3 only of the above records.

Kindly help.

Ankit Mathur
Dec 19 '07 #1
2 8716
deepuv04
227 Expert 100+
Hi Friends,

I have been trying to use this query to get all records that will expire within the next 30 days from the current date.

While I am getting records with the same query what is bothering is I'm also getting records for the days past by.

Expand|Select|Wrap|Line Numbers
  1. Select AccountId, ExpiryDate, DATEDIFF(DAY,GETDATE(),ExpiryDate) as DaysLeft From Usr_Accounts
  2. WHERE Status='Active'
  3. AND DATEDIFF(DAY,GETDATE(),ExpiryDate) < 31
  4.  
Expand|Select|Wrap|Line Numbers
  1.  
  2. Results I'm getting 
  3.  
  4. 000135910 2007-07-31 15:15:21.000 -141
  5. 000280196 2008-01-27 20:02:28.000 39
  6. 000482088 2050-01-01 00:00:00.000 15354
  7. 002100387 2007-08-10 23:59:59.000 -131
  8. 002101119 2007-08-10 23:59:59.000 -131
  9. 002101594 2007-08-10 23:59:59.000 -131
  10. 002101839 2007-08-10 23:59:59.000 -131
  11. 002103017 2007-08-10 23:59:59.000 -131
  12. 002107518 2007-08-10 23:59:59.000 -131
  13. 002107582 2007-08-10 23:59:59.000 -131
  14.  
  15.  
While I require record No. 2 & 3 only of the above records.

Kindly help.

Ankit Mathur
Use one more condition exipiry date > current date in where clause
Select AccountId, ExpiryDate, DATEDIFF(DAY,GETDATE(),ExpiryDate) as DaysLeft From Usr_Accounts
WHERE Status='Active'
AND DATEDIFF(DAY,GETDATE(),ExpiryDate) < 31 and
expiryDate >= GetDate()

the above query will give you the two records you want, but if you want the records that will expire within the next 30 days from the current date. use

DATEDIFF(DAY,GETDATE(),ExpiryDate) between 0 and 30 instead of

DATEDIFF(DAY,GETDATE(),ExpiryDate) < 31 . if you get no result means there are no records which will expire in next 30 days

thanks
Dec 19 '07 #2
Use one more condition exipiry date > current date in where clause
Select AccountId, ExpiryDate, DATEDIFF(DAY,GETDATE(),ExpiryDate) as DaysLeft From Usr_Accounts
WHERE Status='Active'
AND DATEDIFF(DAY,GETDATE(),ExpiryDate) < 31 and
expiryDate >= GetDate()

the above query will give you the two records you want, but if you want the records that will expire within the next 30 days from the current date. use

DATEDIFF(DAY,GETDATE(),ExpiryDate) between 0 and 30 instead of

DATEDIFF(DAY,GETDATE(),ExpiryDate) < 31 . if you get no result means there are no records which will expire in next 30 days

thanks
Thanks Deepu,

I can't believe I was missing out something as simple as
ExpiryDate >= GetDate()

I was trying varying permutations but missed this simple one.

Really appreicate your help.

Thanks
Ankit Mathur
Dec 20 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by rong.guo | last post: by
5 posts views Thread by emanuel.levy | last post: by
6 posts views Thread by Twobridge | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.