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

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

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;

Select allOpen 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

Select allOpen 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!

for mysql

use

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

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?

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

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

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

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)

Select allOpen 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() ?

Thanks!