Solved

monthly report, MySQL - DateTime

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

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
Comment Utility
Take eicheled's answer and throw in the CURRENT_DATE() or NOW() functions, and you have it.
0
 

Expert Comment

by:rotulaus
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

743 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

15 Experts available now in Live!

Get 1:1 Help Now