I think you are very close but you are missing the LAST day of each month. I think you need to change your WHERE to read
WHERE queue_time >= @Start AND queue_time < @BegOfMonth Reasoning: If @BegOfMonth is '2004-09-01' then @End will be '2004-08-31'. If queue_time is a datetime value somewhen during the day of 8/31 (like '2004-08-31 13:15:46') then you miss it with "< @End". Because of the time component, you still miss that event if you change the comparator to "<=". You need to compare to < the next date (@BegOfMonth). '2004-08-31 13:15:46' < '2004-08-31' is false '2004-08-31 13:15:46' <= '2004-08-31' is false '2004-08-31 13:15:46' < '2004-09-01' is true Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Dirk Bremer \(NISC\)" <[EMAIL PROTECTED]> wrote on 09/27/2004 03:18:46 PM: > ----- Original Message ----- > From: <[EMAIL PROTECTED]> > To: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Monday, September 27, 2004 11:40 > Subject: Re: Date Range > > > > To compute the date range for "two months ago". (if the current month is > > September, this will return July's data) > > > > set @dtBegOfMonth = DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 > > DAY) > > set @dtEnd = DATE_SUB(@dtBegOfMonth, INTERVAL 1 month) > > set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 month) > > > > Then, continue with the same query as above. These should be really fast > > as you are comparing constant values against what should be indexed table > > data. You also avoid string conversions, substring comparisons, and other > > data type conversions this way. The Date information stays date > > information.... > > The following produced the result set that I was interested in. > > set @BegOfMonth = DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY); > set @Start = DATE_SUB(@BegOfMonth, INTERVAL 1 month); > set @End = DATE_SUB(@BegOfMonth, INTERVAL 1 day); > > SELECT ident, > job_coop, > cycle, > lpad(status,10,' ') as status, > type, > file_size, > date_format(queue_time,'%Y-%m-%d %T') as queue_time, > file_time, > transfer_start, > transfer_end > FROM queue > WHERE queue_time >= @Start AND queue_time < @End > ORDER BY ident; > > Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters > USA Central Time Zone > 636-922-9158 ext. 8652 fax 636-447-4471 > > [EMAIL PROTECTED] > www.nisc.cc >
