MySql - can I create a Calculated Field and index it?

I have a WHERE clause that looks like:
WHERE Month(PA.EntryDate) = Month('11-10-04') and Year(PA.EntryDate) = Year('11-10-04')

Open in new window


Something like that shows up a lot in my DB.

I would like to add a column
EntryMonth as Month(EntryDate) -- or something to that effect.  In MS SQL Server, that would allow me to index the EntryMonth so that an index could be used in the WHERE clause.

Can I do something similar in MySql?  Every Google search I try on Calculated Fields is coming up with fields updated by a trigger ... not a true calculated field.  Is that the best MySql 5 can offer currently?

Thanks!
LVL 32
Daniel WilsonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ralmadaCommented:
why don't you just index EntryDate and then change your WHERE clause like this?

WHERE PA.EntryDate between dateadd(m, datediff(m, 0, '11-10-04'), 0) and dateadd(m, datediff(m, 0, '11-10-04'), 1)-1
Daniel WilsonAuthor Commented: 
select * from wt_PaymentAgreement PA WHERE PA.EntryDate between dateadd(m, datediff(m, 0, '11-10-04'), 0) and dateadd(m, datediff(m, 0, '11-10-04'), 1)-1;

Open in new window

gives me this:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' '11-10-04'), 0) and dateadd(m, datediff(m, 0, '11-10-04'), 1)-1' at line 1

Open in new window


And ... what is the dateadd (datediff () ) stuff doing?  

This may be a good solution, but I do need to understand it!

Thanks!
ralmadaCommented:
for mysql

use

WHERE PA.EntryDate between last_day('11-10-04' - 1 interval month) + 1 and last_day('11-10-04')
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Get the Guide
Daniel WilsonAuthor Commented:
The DateDiff call is throwing it:
select datediff(m, 0, '2011-10-04');

gives the same error.

I think the syntax you gave me (once we fix whatever the error is) is saying "any date from the first of this month through 1 day less than the first of next month".

right?
Daniel WilsonAuthor Commented:
With the last_day syntax:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'interval month) + 1 and last_day('11-10-04')' at line 1
ralmadaCommented:
problem is the the original suggestion was for MS SQL Server. for MySQL try http:#a36917988

>>I think the syntax you gave me (once we fix whatever the error is) is saying "any date from the first of this month through 1 day less than the first of next month".<<

correct :) basically is a way to obtain the first and last day of the month and then use those to compare. Now if you have time portion involved as well then you might want to just compare to the first day of the next month

WHERE PA.EntryDate >= last_day('11-10-04' - 1 interval month) + 1 and PA.EntryDate < last_day('11-10-04') + 1

ralmadaCommented:
>>ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'interval month) + 1 and last_day('11-10-04')' at line 1
<<

oops, I've inverted things there:


WHERE PA.EntryDate >= last_day('11-10-04' - interval 1 month) + 1 and PA.EntryDate < last_day('11-10-04') + 1

Daniel WilsonAuthor Commented:
I do have the time portion as well ... so ... the +1 makes sense, except it's not working right either.
mysql> select last_day('11-10-04');
+----------------------+
| last_day('11-10-04') |
+----------------------+
| 2011-10-31           |
+----------------------+
1 row in set (0.00 sec)

mysql> select last_day('11-10-04')+1;
+------------------------+
| last_day('11-10-04')+1 |
+------------------------+
|               20111032 |
+------------------------+
1 row in set (0.00 sec)

Open in new window


But the biggest problem in here is the - 1 interval month  That is not being accepted at all.

should I use date_sub() ?
ralmadaCommented:
1 interval month  should be interval 1 month  

WHERE PA.EntryDate >= last_day('11-10-04' - interval 1 month) + INTERVAL 1 DAY and PA.EntryDate < last_day('11-10-04') + INTERVAL 1 DAY

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Daniel WilsonAuthor Commented:
Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.