Solved

How do I return records from a date range with SQL syntax?

Posted on 2007-12-04
6
497 Views
Last Modified: 2010-05-18
I have an SQL table of records from 2006-2007 and I want to select a range of records based upon start and stop dates entered into text boxes.

I'm using Visual Web Developer; SQL Express; Express Management Studio; VB backcoding

Here's my statement:

SELECT [ID], [BILLNUMBER], [CHARGEDATE], [LNAME], [FNAME] FROM [TABLETracking] WHERE (([CHARGEDATE] IS NOT NULL) AND ([CHARGEDATE] >= @CHARGEDATE) AND ([CHARGEDATE] <= @CHARGEDATE2) AND ([7CAT] = @7CAT))


So, the @CHARGEDATE and @CHARGEDATE2 values are coming from textbox dates that are entered. @7CAT is a value from a user-select dropdown list.

However, a start and stop date such as 01/01/2007 to 12/31/2007 will also return records from the year 2006 (arrrghhhh!...frustrating). How can I make this work? I know there is a BETWEEN statement that can be used, but I am unable to get anything to work..... maybe my parentheses are not structured correctly. Thanks for any assistance.
0
Comment
Question by:jazjef
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:cforant
Comment Utility
SELECT column_name FROM table_name
WHERE column_name
BETWEEN value1 AND value2
0
 
LVL 23

Accepted Solution

by:
Ashish Patel earned 500 total points
Comment Utility
Try this.
SELECT [ID], [BILLNUMBER], [CHARGEDATE], [LNAME], [FNAME] FROM [TABLETracking] WHERE [CHARGEDATE] IS NOT NULL AND [CHARGEDATE] BETWEEN @CHARGEDATE AND @CHARGEDATE2 AND [7CAT] = @7CAT

0
 
LVL 4

Author Comment

by:jazjef
Comment Utility
asvforce:

thanks for the reply... your statement works fine, but I still get records from 2006 when I use it.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 23

Expert Comment

by:Ashish Patel
Comment Utility
What is the column type of CHARGEDATE column?
0
 
LVL 4

Author Comment

by:jazjef
Comment Utility
ASVFORCE:

column type was char(10)

this was causing the problem.... when I converted to datetime your solution works.... and so does my own original solution prior to asking the question.... thanks.

I have another problem though.... I will post a question on it momentarily. It's related to this datetime thing...
0
 
LVL 23

Expert Comment

by:Ashish Patel
Comment Utility
Good catch!!! Glad to hear that you have solved this now.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now