troubleshooting Question

SQL BETWEEN statement not capturing 'todays' records

Avatar of Lee R Liddick Jr
Lee R Liddick JrFlag for United States of America asked on
Microsoft SQL ServerSQL
4 Comments1 Solution503 ViewsLast Modified:
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>
ASKER CERTIFIED SOLUTION
Sharath S
Data Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros