How to group an SQL date column stored as int as Year Month using LINQ-CodePudding

I have a column in SQL with a date value (CreDt) stored as an int.

the below Linq2Sql query syntax will group my table by the CreDt date. i.e 20220109 which is not what I want, I want to group it by Year or by YearMonth. i.e 202201

how can I do that?

var yymm = from p in Order
              group p by new { ym = p.CreDt } into d
              select new { YearMonth = d.Key.ym};

CodePudding user response：

Dropping digits from a number is easy, just divide the date by 100 when you group. The day will be truncated due to integer division.

20220109 / 100 = 202201.09
               = 202201

group p by new { ym = p.CreDt / 100 } into d

If you want to group by year, divide by 10000.

CodePudding user response：

In SQL, you can use the EOMONTH function:

SELECT
  YearMonth = EOMONTH(p.CreDt)
FROM Order p
GROUP BY
  EOMONTH(p.CreDt);

However, in Entity Framework, I don't believe this function has been made available. You should in theory be able to declare it as a custom function.

Guessing in the dark here, but looks like you can add this to your .edmx

<Function Name="EOMONTH" ReturnType="datetime" BuiltIn = true>
  <Parameter Name="startDate" Mode="In" Type="dateTime" />
</Function>

And this function

[EdmFunction("EOMONTH", "EoMonth")]
public static dateTime? EoMonth(datetime? startDate)
{
    throw new NotSupportedException("Direct calls are not supported.");
}

Then you can do

var yymm =
    from p in Order
    group p by EoMonth(p.CreDt) into d
    select d.Key;