At 14:50 -0400 9/27/04, [EMAIL PROTECTED] wrote:
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,
Okay. I'll amend my statement to: You either need semicolons at the end
of each statement, or you need to issue each statement separately if you're
using a client that doesn't understand statement terminators. :-)
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
