SQL BETWEEN statement not capturing 'todays' records
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 EndThisQuarterWHERE ...<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>
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'
Lee R Liddick Jr
ASKER
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...