Using SubQueries with Between

[SyntaxTerror]

Hello What would be the best way to implement this where statement pseudocode with proper syntax? Basically, I'm trying to pull dates out of a temp table I've created using subqueries

WHERE CreatedDateTime BETWEEN (SELECT StartDate FROM #temp) AND (SELECT EndDate FROM #temp)

The CreatedDateTime, StartDate, and EndDate are all smalldatetime datatypes.

When I attempt to run this code, I get the error, "Subquery returned more than 1 value. This is not permitted when the subqury follows =, !=, <, <=, >, >= or when the subquery is used as an expression."

Thanks for the help

 

[gmmastros]

It all depends on what you are trying to accomplish and whether there is a relationship between the real table and the temp table.

For sure... one way that would work is...

WHERE CreatedDateTime BETWEEN (SELECT [!]Min([/!]StartDate[!])[/!] FROM #temp) AND (SELECT [!]Max([/!]EndDate[!])[/!] FROM #temp)

This will allow the query to execute, but there certainly is no guarantee that it will return the correct data either. If this does not return the correct data, please provide some sample data and expected results.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SyntaxTerror]

Hm... That does get it to run, however it's odd that I'd need to use an aggregate function on the data since the StartDate and EndDate values are a single date each (I don't care what the error message says, it's not a column with multiple values, it's a single date for each variable)

Just so I know for future reference, why do I have to do that?

 

[gmmastros]

(I don't care what the error message says, it's not a column with multiple values, it's a single date for each variable)

How many rows are in the #temp table? If there is more than 1 row, you will get this error.

I don't understand what you mean by "for each variable". Can you explain that?

Just so I know for future reference, why do I have to do that?

Whenever you use a sub query, you can only return one column and one row. By using the max and min aggregates, you are guaranteed to only get one value. This is why I almost never use sub-queries. Since you did not include any sample data (and expected results), it's not possible for me to show you an alternate query that would return the same results but without using a sub query.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom