From: Mike Koponick (mikeredhawk.info)
Date: Thu Jul 01 2004 - 17:02:49 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Michael/Wesley,

Thanks for your help. You got me going in the right direction!

Thanks again!!!

Mike

-----Original Message-----
From: Michael Stassen [mailto:Michael.Stassenverizon.net]
Sent: Wednesday, June 30, 2004 7:37 PM
To: Mike Koponick
Cc: mysqllists.mysql.com
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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]