Can you modify the table? Where I work, I have complete control of the database. I would separate the key out to three different fields and use a composite index.

Something...

what is the data type for the column [Date_in_char]? If it's a DATE, then that should work. If it's DATETIME, or SMALLDATE, then try this:

SELECT

Sum([DailyValue]) as MTDValue, [Date_in_char]

from

[TEST].[dbo].[test]

Where...

We use Dataflux.

You might want to try removing the single quotes from the following:

WHERE [Date_in_char] BETWEEN 'DATEADD(dd,-(DAY(DATEADD(mm,1,[Date_in_char]))-1),DATEADD(mm,0,[Date_in_char]))'

If you're not updating or inserting into the master table, you could put it on a read-only file group.