Michael/Wesley, Thanks for your help. You got me going in the right direction!
Thanks again!!! Mike -----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 30, 2004 7:37 PM To: Mike Koponick Cc: [EMAIL PROTECTED] Subject: Re: Auto Date selection and format Mike Koponick wrote: > Hello all, > > I would like to be able to select the certain dates within my script. > > select created_date, status, user, comment1, comment7, comment8, action > from users WHERE customerid = 'Customer' AND created_date BETWEEN > '2004-05-31' AND '2004-07-01' ORDER BY created_date, status into outfile > 'test5.txt' FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\"' LINES > TERMINATED BY '\n'; I believe your INTO OUTFILE clause is mispalced. <http://dev.mysql.com/doc/mysql/en/SELECT.html> > > I would like the first date to be the last day of the previous month and > the second date to be the first day of the current month. What is the > most effecient way to do this in my script rather than hard coding? Your description doesn't quite match your example. I'll assume the example is right. I don't know about "most efficient", but you can do it with a combination of date functions. <http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html> > Also, I looked for a way to format the output date to MM/DD/YY rather > than YYYY-MM-DD. Any suggestions? DATE_FORMAT() > Thanks in advance. > > Mike I'm doing this in 2 queries with user variables to try to cut down on ugliness. You could do it in one query by replacing the variables with their definitions in the WHERE clause. SELECT @day:= DAYOFMONTH(CURDATE()) day, @start:= CURDATE() - INTERVAL 1 MONTH - INTERVAL @day DAY start, @end:= CURDATE() - INTERVAL (@day-1) DAY end; +------+------------+------------+ | day | start | end | +------+------------+------------+ | 1 | 2004-05-31 | 2004-07-01 | +------+------------+------------+ 1 row in set (0.00 sec) SELECT DATE_FORMAT(created_date, '%m/%d/%y') AS created, status, user, comment1, comment7, comment8, action INTO OUTFILE 'test5.txt' FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'; FROM users WHERE customerid = 'Customer' AND created_date BETWEEN @start AND @end ORDER BY created_date, status Are you aware that BETWEEN is inclusive? That is, this query will include rows from 5/31 and 7/01. With mysql 4.1.1 or later, you could simplify the variable definitions slightly: SELECT @start:= LAST_DAY(CURDATE() - INTERVAL 2 MONTH), @end:= LAST_DAY(CURDATE() - INTERVAL 1 MONTH) + INTERVAL 1 DAY; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
