Solved

monthly report, MySQL - DateTime

Posted on 2004-03-30
5
1,031 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql ide 10 55
How do I Enable submit button only if listbox has items 4 64
Dynamic Table mySQL stored procedure 5 63
Group By Function Required in PDF Output 21 65
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
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://…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

739 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