I'm a little confused as to why you are trying to get the list of ids to begin with...could you not do something like... CREATE sp_getfoobar @datein datetime AS DECLARE @dateend datetime SET @dateend = DATEADD(day, 1, @datein) SELECT stuff FROM logs WHERE somedate >= @datein AND somedate < @dateend
If the @datein is just the date part, then the time part will default to midnight, and the above will pull back everything in that day. As for using the data from a previous query...you can do that by setting a variable or using a cursor, as was previously noted...but also you could possibly make use of a temporary table... SELECT foo INTO #temptable FROM foobar where foo = 'blah' SELECT foo2 FROM foobar2 WHERE foo IN (SELECT foo from #temptable) On 2/22/06, Michael Dinowitz <[EMAIL PROTECTED]> wrote: > I've answered my own question. Between is faster by far. > > I was trying to get all of the records for a specific day from a large DB > and it was taking forever even though the created (datetime) field was > indexed. DateDiff(d, created, @datein) took waaay too long. I tried to do a > query to get the start and end ids for the specific date to do a between but > have no clue how to pass the date from one query to another within a SP. > My final solution was to turn the dates into integers and compare those. Not > great, but.... > > If anyone knows how todo a query that uses info from another query in the > same SP, please let me know. > Thanks > > > >I have some huge logs and I want to get specific data from them. I have 2 > >choices on how I can get all of the entries for a single day. The first > >choice is to do a datediff between the day I want and the date in the > >created field. The second is to do one query to get the min and max for a > >specific date and then do a second query to get all records between these > >two numbers. > > Logically, the first should be faster, but is it? > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2440 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
