?
Solved

vb.net 2003.  Filter Dataset on a datevalue field to capture between 30-60 days from the inv date.

Posted on 2007-10-18
9
Medium Priority
?
515 Views
Last Modified: 2012-06-27
vb .net 2003.  I would like to filter a dataset that has a invoice due date and do an aging report.  Filter for records that are between 30-60 days past the invoice due date.  How can I do this?
Thanks
0
Comment
Question by:mphillip85
  • 5
  • 4
9 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 20104570
Are you looking to do this on the back-end database?  What database type are you connecting to?

Bob
0
 

Author Comment

by:mphillip85
ID: 20105306
SQL 2005 server.  I would prefer to pass the criteria through the dataset commandtext.  Otherwise I will have to create several datasets to connect to the view on the server.  I know that it will speed things up on the backend since it is a 8 processor server 8 gig ram. scsi drives.
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 2000 total points
ID: 20105405
T-SQL has the DATEADD function that can do what I think you need:

SELECT *
  FROM Invoices
  WHERE DueDate BETWEEN DATEADD(day, -60, DueDate) AND DATEADD(day, -30, DueDate)

Bob
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:mphillip85
ID: 20105572
I am using this:

InvPaymentDueDate BETWEEN DATEADD(day,-59,InvPaymentDueDate) AND
DATEADD(day,-30,InvPaymentDueDate) ORDER BY InvPaymentDueDate

I even put the -30 to -1 and I know I have invoices that fall in that area and it did not get them
What am I doing wrong?

The InvPaymentDueDate >= DATEADD(day,-90,InvPaymentDueDate)  seems to take all the records.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 20105694
Since I can't see the data, and I don't know about localization settings for your SQL Server instance, it is tough for me to spot any reason for your observations.

Bob
0
 

Author Comment

by:mphillip85
ID: 20106575
So I guess I will try in the view and see what results I get, on the server and trace it from there.
0
 

Author Comment

by:mphillip85
ID: 20106927
Whats the best way to actually see the query string results to see if the values are correct?
0
 

Author Comment

by:mphillip85
ID: 20106962
Can you give me an example of what I should expect.  Such as with your syntax replace the values that would actually be filtered against, since I must be missing some logic somewhere.  
Thanks.  
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 20107541
1) Is this SQL Server 2005 Express Edition?

2) Do you have SQL Server Management Studio installed?

Bob
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question