How to extract database info from current month and year
I want to now SELECT all records in the month that it is at the time of viewing the page. So, if I view today, it should show me all records for March 2017. If I view in April, then it should show me all records for April 2017and not April for every year i.e. 2016, 2015, 2014 etc.
In my SELECT statement I don't know if that is where I should try to strip out the day and just extract the month and year or if that would have to be done elsewhere.
I can get the current month and year like:
$current_month = date("m");
$current_year = date("Y");
But then I am not sure how to handle the SQL. I tried something like this but no joy.
SELECT `lost`, EXTRACT (YEAR FROM `summary`) AS currentyear WHERE currentyear = ?
Somewhere on this site there is an article with a title like "Beware of Between" that might be worth a look. Without your test data we can't be sure anything will work as we have assumed. Just a thought for going forward in the Q&A process: SSCCE.
I think the real problem with BETWEEN goes a bit beyond indexes and performance to the issue of exact output. Here's my concern. It's subtle but I've seen this issue in real life.
Let's say you have columns of type DATE and your BETWEEN clause identifies ISO-8601-standard start and end dates. Things work perfectly.
Now let's say you ALTER TABLE and change the column definition from DATE to DATETIME. Instead of something like 2017-03-31, newly inserted rows will potentially have additional time information, something like 2017-03-31 14:56:00. If your query says BETWEEN ... AND '2017-03-31' you will not get the rows from 2017-03-31 that have the additional TIME information. You can get this to work correctly if you use the DATE() function on the DATETIME columns. Or you can get this to work correctly if you add 23:59:59 to the end of the last date in the BETWEEN clause.
This is not a problem for the first date, only the last date in the BETWEEN clause.
https://bugs.mysql.com/bug.php?id=58190
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Thanks for using Experts Exchange.
Please provide your email to receive a sample view!
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.