Help needed in this DATEDIFF SQL Query

[ankitmathur]

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.

Select AccountId, ExpiryDate, DATEDIFF(DAY,GETDATE(),ExpiryDate) as DaysLeft From Usr_Accounts
WHERE Status='Active'
AND DATEDIFF(DAY,GETDATE(),ExpiryDate) < 31
 

 
 Results I'm getting 
 
000135910 2007-07-31 15:15:21.000 -141
000280196 2008-01-27 20:02:28.000 39
000482088 2050-01-01 00:00:00.000 15354
002100387 2007-08-10 23:59:59.000 -131
002101119 2007-08-10 23:59:59.000 -131
002101594 2007-08-10 23:59:59.000 -131
002101839 2007-08-10 23:59:59.000 -131
002103017 2007-08-10 23:59:59.000 -131
002107518 2007-08-10 23:59:59.000 -131
002107582 2007-08-10 23:59:59.000 -131
 
 

While I require record No. 2 & 3 only of the above records.

Kindly help.

Ankit Mathur

[deepuv04]

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.

Select AccountId, ExpiryDate, DATEDIFF(DAY,GETDATE(),ExpiryDate) as DaysLeft From Usr_Accounts
WHERE Status='Active'
AND DATEDIFF(DAY,GETDATE(),ExpiryDate) < 31
 

 
 Results I'm getting 
 
000135910 2007-07-31 15:15:21.000 -141
000280196 2008-01-27 20:02:28.000 39
000482088 2050-01-01 00:00:00.000 15354
002100387 2007-08-10 23:59:59.000 -131
002101119 2007-08-10 23:59:59.000 -131
002101594 2007-08-10 23:59:59.000 -131
002101839 2007-08-10 23:59:59.000 -131
002103017 2007-08-10 23:59:59.000 -131
002107518 2007-08-10 23:59:59.000 -131
002107582 2007-08-10 23:59:59.000 -131
 
 

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

[ankitmathur]

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