Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

monthly report, MySQL - DateTime

Posted on 2004-03-30
5
Medium Priority
?
1,034 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
3 Comments
 
LVL 3

Accepted Solution

by:
eicheled earned 80 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month8 days, 23 hours left to enroll

877 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