Between Date1 & Date2 problem

[hambstack]

Hi. I'm having a problem with finding all results between two dates. Below is the code I'm currently using, which isn't doing exactly what I need it to do. For example, if a search is done for all entries for January 1-March 15, I get the total results for only Jan. 1-15 and March 1-15.

SELECT COUNT(ID) AS TotalLogs, SUM(QuoteValue) AS QuoteValueTotal, SUM(OrderValue) AS OrderValueTotal
FROM Intranet2.dbo.QLConversions
WHERE (YEAR(DateStarted) = #form.Year#) AND
(MONTH(DateStarted) BETWEEN #form.Month1# AND #form.Month2#) AND (DAY(DateStarted) BETWEEN #form.Day1# AND #form.Day2#)

I tried the following code next:

SELECT COUNT(ID) AS TotalLogs, SUM(QuoteValue) AS QuoteValueTotal, SUM(OrderValue) AS OrderValueTotal
FROM Intranet2.dbo.QLConversions
WHERE DateStarted Between
(select DateStarted
from Intranet2.dbo.QLConversions
WHERE (YEAR(DateStarted) = #form.Year#) AND
(MONTH(DateStarted) = #form.Month1#) AND
(DAY(DateStarted) = #form.Day1#))
AND
(select DateStarted
from Intranet2.dbo.QLConversions
WHERE (YEAR(DateStarted) = #form.Year#) AND
(MONTH(DateStarted) = #form.Month2#) AND
(DAY(DateStarted) = #form.Day2#))

But I get the following error if I use the above code:
"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

Can someone help me out?

Thanks,
Kim

 

[bperry]

I think we are looking bor an approach that goes more like this:

....
WHERE DateStarted BETWEEN
Cast(#form.Year# + form.Month1# + #form.Day1# as datetime)
AND
Cast(#form.Year# + form.Month2# + #form.Day2# as datetime)

But I'm not familiar with what product uses # #, so I'm not sure that this will be valid syntax. Try it if you like.

I'm thinking that this is the approach you want, although that specific syntax will fail... Hopefully you will know, or maybe another forum poster can show us how.

 

[hambstack]

Thanks, bperry. The # signs are ColdFusion tags for variables. Your code got rid of the error, but it is in fact failing (gives zero results). I'll try working with it.

 

[bperry]

Soneone here might post a suggestion. I'm wondering if someone in the Cold Fusion forum would know just what we want?

 

[hambstack]

bperry,
Thanks for the suggestion of checking the ColdFusion forum. The line between SQL and ColdFusion questions is so fuzzy!

I searched the ColdFusion forum and found something that ended up working for me. This is what it was:

<!--- set temp date --->
<CFSET temp.begindate = &quot;#form.Month1#/#form.Day1#/#form.Year#&quot;>
<CFSET temp.enddate = &quot;#form.Month2#/#form.Day2#/#form.Year#&quot;>

<!--- WHERE statement --->
WHERE DateStarted BETWEEN #createodbcdate(temp.BeginDate)#
AND #createodbcdate(temp.EndDate)#

Thanks again,
Kim

 

[bperry]

okay, great.