Paul DuBois <[EMAIL PROTECTED]> wrote on 09/27/2004 02:07:54 PM: > At 12:57 -0500 9/27/04, Dirk Bremer (NISC) wrote: > >----- 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 > > > > > >> I see that you are on 4.0.18 so you can't use many of the new date > >> functions (4.1.1+) but has to be an easier way. Let's try this for a > >> "yesterday" query > >> > >> set @dtEnd = CURDATE() > >> set @dtStart = DATE_SUB(@dtEnd, 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 >= @dtStart > >> AND queue_time < @dtEnd > >> > >> > >> 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.... > >> > > > >Shawn, > > > >Thanks for the advice. Unfortunately, your example produces the following > >error. Looking at it, I can see nothing obvious that is causing the error. > > > >ERROR 1064: You have an error in your SQL syntax. Check the manual that > >corresponds to your MySQL server version for the right syntax to use near > >'set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 DAY) > > You need semicolons at the end of each statement. > > > -- > Paul DuBois, MySQL Documentation Team > Madison, Wisconsin, USA > MySQL AB, www.mysql.com
Whether he needs semicolons depends on which tool he is using to execute his statements. If he is using the MySQL client or any of several other tools, I agree. If he is going through an ODBC connection, he doesn't need them. (At least my 3.52.x drivers can't accept more than one statement per request.) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
