Solved

monthly report, MySQL - DateTime

Posted on 2004-03-30
5
1,028 Views
Last Modified: 2010-05-18
Hi,
I have my_date field in my table that contains DATETIME format (2004-03-01 17:34:57) for every uploaded document. Now, every month I want to retrieve documents in the range of
 2004-currentMonth-01   through  2004-currentMonth-30. So, basicly it's kind of monthly report for current month only beginning with very first day and ending with very last day of the same month.

I could probably specify my date and time every month, changing my WHERE .... condition every month mannually, but I believe there is an automated way of doing it with MySQL Version 4.0.18; and your help is much appreciated.


Thanks.
0
Comment
Question by:shmoel
5 Comments
 
LVL 3

Accepted Solution

by:
eicheled earned 20 total points
ID: 10717251

From the MySQL manual, is this what you are after???

LAST_DAY(date)
    Takes a date or datetime value and returns the corresponding value for the last day of the month. Returns NULL if the argument is invalid.

mysql> SELECT LAST_DAY('2003-02-05');
        -> '2003-02-28'
mysql> SELECT LAST_DAY('2004-02-05');
        -> '2004-02-29'
mysql> SELECT LAST_DAY('2004-01-01 01:01:01');
        -> '2004-01-31'
mysql> SELECT LAST_DAY('2003-03-32');
        -> NULL

    LAST_DAY() is available as of MySQL 4.1.1.
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 10719877
Take eicheled's answer and throw in the CURRENT_DATE() or NOW() functions, and you have it.
0
 

Expert Comment

by:rotulaus
ID: 10723944
Suppose that you have:
startDate & endDate DATETIME formated variables for begin and end of mounth:

SELECT * FROM my_table WHERE my_date BETWEEN startDate  AND endDate ORDER BY my_date ASC
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question