I am curious if I can call the DATENAME function with the DbFunctionCall method. If so what would this syntax look like?

Here is my first pass (that fails):

ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(PayrollDetailFields.PayDate, 0, "PayMonth");
fields.DefineField(new EntityField2("Gross", PayrollDetailFields.BasePay + PayrollDetailFields.RegularPay), 1);


fields["PayMonth"].ExpressionToApply = new DbFunctionCall("DATENAME", new object[] {"MM", PayrollDetailFields.PayDate });

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(PayrollDetailFields.SeparationId == 59);

IGroupByCollection group = new GroupByCollection();
group.Add(fields[0]);

DataTable results = new DataTable();
using (DataAccessAdapterBase adapter = AdapterFactory.CreateAdapter())
{
    adapter.FetchTypedList(fields, results, bucket, 0,null, false, group);
}

This results in System.Data.SqlClient.SqlException: Invalid parameter 1 specified for datename.

And the TSQL Looks like this:

Generated Sql query: 
    Query: SELECT DISTINCT DATENAME(@LOce0db7e31, [EVDB].[dbo].[PayrollDetail].[PayDate]) AS [PayMonth], [EVDB].[dbo].[PayrollDetail].[BasePay] + [EVDB].[dbo].[PayrollDetail].[RegularPay] AS [Gross] FROM [EVDB].[dbo].[PayrollDetail]  WHERE ( ( [EVDB].[dbo].[PayrollDetail].[SeparationId] = @SeparationId2)) GROUP BY DATENAME(@LOce0db7e33, [EVDB].[dbo].[PayrollDetail].[PayDate])
    Parameter: @LOce0db7e31 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "MM".
    Parameter: @SeparationId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 59.
    Parameter: @LOce0db7e33 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "MM".

MM is interpreted as a string, not a reserved word. so datename('MM', PayDay) is emitted not dateaname(MM, PayDay).

try this instead

fields["PayMonth"].ExpressionToApply = new DbFunctionCall("Month", new object[] { PayrollDetailFields.PayDate });

then convert 1-12 to Jan. - Dec. in your business logic or presentation

Thanks for the help, but the solution is not elegant. I think a retreival proc is in order.

HEre is the TSQL:

Select Month(PayDate) as [Month], Year(PayDate) as [Year], DATENAME(MM, PayDate) PayMonth, SUM(BasePay + RegularPay) As Gross From dbo.PayrollDetail Where SeparationId = 7 And (PayDate Between DATEADD("Month", -11,GetDate()) And GetDate()) Group By Year(PayDate), Month(PayDate), DATENAME(MM, PayDate) Order By Year(PayDate), Month(PayDate)

This is supported in v2.5's update to DbFunctionCall (now in beta)