Alternatively, with some databases you can cast the date as an integer. Fractional amounts (i.e. times within the day) fall away. Makes comparisons and grouping by day very easy.
For instance, right now in SQL Server: select cast( getdate() as int ) yields 38774 > > select cast( 0 as datetime ) yields 1/1/1900 12:00 AM > Everything can thus be reduced to simple integer comparisons. -- Ken On 2/23/06, Maureen <[EMAIL PROTECTED]> wrote: > > You can return the query in the cursor then loop over the cursor and > use the values returned in the second query, much the same as you > would loop over a cfquery. > > If you want all dates that match one day why not just > > select where (day(datein) = dayUwant) and (month(datein) = > monthUwant) and (year(datein) = yearUwant) > > > 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:2443 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
