473,699 Members | 3,239 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help needed in this DATEDIFF SQL Query

36 New Member
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 8873
deepuv04
227 Recognized Expert New Member
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,GE TDATE(),ExpiryD ate) as DaysLeft From Usr_Accounts
WHERE Status='Active'
AND DATEDIFF(DAY,GE TDATE(),ExpiryD ate) < 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,GE TDATE(),ExpiryD ate) between 0 and 30 instead of

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

thanks
Dec 19 '07 #2
ankitmathur
36 New Member
Use one more condition exipiry date > current date in where clause
Select AccountId, ExpiryDate, DATEDIFF(DAY,GE TDATE(),ExpiryD ate) as DaysLeft From Usr_Accounts
WHERE Status='Active'
AND DATEDIFF(DAY,GE TDATE(),ExpiryD ate) < 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,GE TDATE(),ExpiryD ate) between 0 and 30 instead of

DATEDIFF(DAY,GE TDATE(),ExpiryD ate) < 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

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

Similar topics

2
1187
by: rong.guo | last post by:
I am trying to use table 'Account' and table 'Balance' to get the ideal result table listed below, but my query is too cumbersome. If an account opened 10 years ago, I would have 12*10=120 rows in my query. Is there any simplied way to do this, like a do-loop thing? Thanks a million! Table Account account_no open_date 1111 1/1/2000 2222 2/1/2002
1
1812
by: rsarath | last post by:
Hello, I have the following setup and I would appreciate any help in improving the performance of the query. BigTable: Column1 (indexed) Column2 (indexed) Column3 (no index) Column4 (no index)
1
4982
by: PMBragg | last post by:
ORINGINAL Post >Thank you in advance. I'm trying to pull all inventory items from December >of the previous year back to 4 years for my accountant. I know this can be >done, but I'm drawing a blank. I've tried; > >DateDiff("y",-4,DateIn) and get errors > >Please any assistance would be greatly appreciated. >
1
2344
by: Kevin | last post by:
Group By - Client Sum - PastDue: Iif(DateDiff("d",,Date())>30,,0) Sum - Current: Iif(DateDiff("d",,Date())<31,,0) These 2 sum fields work fine, EXCEPT it is not summing my negative numbers/credits. Can not understand why?
5
1557
by: emanuel.levy | last post by:
I have a table called tbl_employers. One of the fields is start_date. I'm trying to make a query that will show all entries where todays date is 275 days and 305 days after the start date. Any help would be appreciated
2
1611
by: rdraider | last post by:
Hi, We have a lame app that uses 2 datetime(8) fields, 1 stores the date, the other the time. example query: select aud_dt, aud_tm from orders results: aud_dt aud_tm
6
2136
by: Twobridge | last post by:
I hope someone can help me out with my problem. I have found a sql statement that basically pulls all bills filed within a certain time period and the payments made on those bills with in the same time period. I group the payments by payment year and filed year which gives me a matrix with the filed year as the row and the pay year as the column....and this appears fine. My problem is that my employer does not want to see the...
7
7241
by: Techhead | last post by:
I have a date/time field with a sql format of "datetime" The actual date/time data format is MM/DD/YYYY^hh:mm:ss:pm or "1/25/2007 12:00:16 AM" Both the date and time are combined on the same field with a space seperating the two. I need to run a query on this date/time field using the criteria of today's date so if I want to search all records with todays date, I need to query on this field. I don't need the time, but just today's date...
3
12171
by: Dinesh | last post by:
Hi Experts, I am working on SSRS 2005, and I am facing a problem in counting the no of days. My database has many fields but here I am using only two fields They are Placement_Date and Discharge_Date If child is not descharged then Discharge_Date field is empty. I am writing below query to count the number of days but is is not working it is showing the error "The conversion of a char data type to a datetime data type resulted
0
8706
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8633
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9055
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8947
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
5891
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4392
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4642
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2366
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2016
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.