Solved

monthly report, MySQL - DateTime

Posted on 2004-03-30
5
1,025 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now