What argument should I use with SqlFunctions.DateName to get the month like "MM"?

Question

Is it possible with SqlFunctions.DateName (or any other SqlFunctions method) to get the month in two digits? eg: August --> "08"

I tried all these:

  test1 = SqlFunctions.DateName("MM", e.Date),
  test2 = SqlFunctions.DateName("m", e.Date),
  test3 = SqlFunctions.DateName("mm", e.Date),

But they all return "August"

There is also SqlFunctions.DatePart but it returns an integer, eg: SqlFunctions("mm", e.Date) returns the integer 8

Answer 1

You can do it like this:

.Select(c => DbFunctions.Right("00" + c.Date.Month, 2))

RIGHT (SQL function) returns x rightmost characters from given string (2 in this case) and we prepend month with 2 zeroes so that if month has 1 digit, result is "00x" so "0x" is returned, and when month has 2 digits - result is "00xx" and "xx" is returned.

The resulting sql query will be just:

RIGHT(N'00' + CASE WHEN (DATEPART (month, [Extent1].[Date]) IS NULL) 
    THEN N'' ELSE  CAST( DATEPART (month, [Extent1].[Date])

As a bonus, this code is not sql-server specific and will work with other providers.