Solved

SQL BETWEEN statement not capturing 'todays' records

Posted on 2011-03-09
4
482 Views
Last Modified: 2012-06-27
I have a SQL statement with a BETWEEN date WHERE clause and it is not capturing those records with the same 'end' date.

My database column is a date/time with values of the date and time...12/31/2011 12:13:23 PM.

So I understand why it is not capturing it because the query is not pulling information after midnight on the day because my end date in the query shows 00:00:00.0.

For example, in my table it would have the following values:

ID               dtiResponseDate
1                  03/01/2011 3:34:42 PM
2                  12/31/2010 9:23:53 AM
3                 01/23/2011 10:29:39 PM

When I run my query looking for the dates between 10/01/2010 and 12/31/2010 (representing records from the last quarter), I should get record ID #2 as a result.  If I run the query for dates 01/01/2011 and 03/31/2011 (representing records from this quarter), I should get records #1 and #3 as a result.

What do I need to do in order to capture that record with the same date as my 'end date' in the BETWEEN statement of my WHERE clause?
<!---- this is part of my SELECT and WHERE clause which captures the dates for the current year, last quarter, and current quarter ---->
SELECT ...
DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) as BeginCurrentYear,
DATEADD(yy,DATEDIFF(yy,-1,GETDATE()),-1) as EndCurrentYear,
DATEADD(qq,DATEDIFF(qq,0,GETDATE())-1,0) as BeginLastQuarter,
DATEADD(qq,DATEDIFF(qq,0,GETDATE()),-1) as EndLastQuarter,
DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0) as BeginThisQuarter,
DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),-1) as EndThisQuarter
WHERE ...
<cfif cal is 'yy'>
AND REQ.dtiResponseDate BETWEEN 
	<CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#getDates.BeginCurrentYear#"> AND 
	<CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#getDates.EndCurrentYear#">
<cfelseif cal is 'lq'>
AND REQ.dtiResponseDate BETWEEN
	<CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#getDates.BeginLastQuarter#"> AND 
	<CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#getDates.EndLastQuarter#">                
<cfelse>
AND REQ.dtiResponseDate BETWEEN
	<CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#getDates.BeginThisQuarter#"> AND 
	<CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#getDates.EndThisQuarter#">                
</cfif>

Open in new window

0
Comment
Question by:Lee R Liddick Jr
  • 2
4 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35085281
That is because you're searching for values that are previous to 12/31/2011 00:00:00. Instead of just specifying the date, you have to specify it like: '12/31/2011 23:59:59'
0
 

Author Comment

by:Lee R Liddick Jr
ID: 35085647

So I understand why it is not capturing it because the query is not pulling information after midnight on the day because my end date in the query shows 00:00:00.0.

Yeah I know...as I already said...the issue is...I'm not sure how to get the 'time' incorporated into this so it doesn't leave out those records...
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35086223
To get rid off the time part, you can try like this.
select dateadd(dd,0,datediff(dd,0,getdate())) -- 2011-03-09 00:00:00.000

Open in new window


So, in your code, you can compare only date portions.
WHERE ...
<cfif cal is 'yy'>
AND dateadd(dd,0,datediff(dd,0,REQ.dtiResponseDate)) BETWEEN 
	<CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#getDates.BeginCurrentYear#"> AND 
	<CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#getDates.EndCurrentYear#">
<cfelseif cal is 'lq'>
AND dateadd(dd,0,datediff(dd,0,REQ.dtiResponseDate)) BETWEEN
	<CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#getDates.BeginLastQuarter#"> AND 
	<CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#getDates.EndLastQuarter#">                
<cfelse>
AND dateadd(dd,0,datediff(dd,0,REQ.dtiResponseDate)) BETWEEN
	<CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#getDates.BeginThisQuarter#"> AND 
	<CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#getDates.EndThisQuarter#">                
</cfif>

Open in new window

0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35086857
Sorry, I hadn't noticed that.

You can also use, which I think is simpler, a cast. Just cast the value as date (as opposed to datetime) and it should be rid of the time part.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

920 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

16 Experts available now in Live!

Get 1:1 Help Now