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
2 8877 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. -
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,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
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
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
|
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)
|
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.
>
|
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?
|
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
| |
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
|
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...
|
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...
|
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
|
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,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
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,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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();...
|
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...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
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...
| |