----- Original Message -----
From: <SGreen@stripped>
To: "Dirk Bremer (NISC)" <dirk.bremer@stripped>
Cc: <mysql@stripped>
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)
Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters
USA Central Time Zone
636-922-9158 ext. 8652 fax 636-447-4471
dirk.bremer@stripped
www.nisc.cc
