Linq 2 Sql DateTime format to string yyyy-MM-dd

Question

Basically, i need the equivalent of T-SQL CONVERT(NVARCHAR(10), datevalue, 126)

I've tried:

1. from t in ctx.table select t.Date.ToString("yyyy-MM-dd") but it throws not supported exception
2. from t in ctx.table select "" + t.Date.Year + "-" + t.Date.Month + "-" + t.Date.Day but i don't think it's an usable solution, because i might need to be able to change the format.

The only option I see is to use Convert.ToString(t.Date, FormatProvider), but i need a format provider, and I'm not sure it works either

FormatProvider doesn't work, String.Format doesn't work (string.Format("{0:yyyy-MM-dd}", t.Date) throws not supported exception too).

Answer 1

Assuming that t.Date is nullable (DateTime?) this could be the problem, try using:

from t in ctx.table select (t.HasValue ? t.Date.Value.ToString("yyyy-MM-dd") : string.Empty );

Edit: Second try

The problem is the translation to SQL; it tries to translate the .ToString() to an SQL representation, and fails. So if you should do the following it should work:

(from t in ctx.table select t.Date).ToList().Select(d => d.ToString("yyyy-MM-dd"))

Or

(from t in ctx.table select t.Date).AsEnumerable().Select(d => d.ToString("yyyy-MM-dd"))

AsEnumerable() transforms the previously used IQueryable into an IEnumerable, thus stopping the generation of the SQL (in case of Linq to SQL) or any other transfromation by the provider implementing the specific IQueryable (e.g. Linq to SQL Provider).

Note, before calling AsEnumerable() you should have completed any actions that you want to be converted to SQL and executed on the database directly.