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, statusAre 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]
