We help IT Professionals succeed at work.

How to extract database info from current month and year

Black Sulfur
Black Sulfur asked
on
138 Views
Last Modified: 2017-03-26
I am storing records with a date value in the format: yyyy-mm-dd (2017-03-25).

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");

Open in new window


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 = ?

Open in new window

Comment
Watch Question

Author

Commented:
I also tried DATEPART

SELECT DATEPART(yyyy, prod_date) as year FROM summary

Open in new window

Most Valuable Expert 2011
Author of the Year 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
This is why you have 104 points for PHP :)
Most Valuable Expert 2011
Author of the Year 2014

Commented:
Yes, years of drudgery :-)

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.
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Most Valuable Expert 2011
Author of the Year 2014

Commented:
If performance is an issue, you can verify the way SQL is handling your query with EXPLAIN SELECT.

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.
Most Valuable Expert 2011
Author of the Year 2014

Commented:
Looks like the "can't use indexes" was a regression bug that is now fixed.
https://bugs.mysql.com/bug.php?id=58190

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why 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.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
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
Unlock the solution to this question.
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.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.