Substring of a DateTime with Linq to Sql extensions

Question

I'm trying to get a list of dates from my table, which contains a number of DateTime values in a column called StartTime. My predecessor was using the following SQL:

SELECT DISTINCT SUBSTRING(CONVERT(VARCHAR(50), StartTime, 120), 1, 10)

This results in a distinct list of dates in "yyyy-MM-dd" format for each row in the table. I'm trying to convert this to Linq-to-SQL by doing the following:

query.Select(o => o.StartTime.ToString("yyyy-MM-dd")).Distinct()

However this results in an error "Method 'System.String ToString(System.String)' has no supported translation to SQL."

How can I do this Substring/Convert using Linq-to-SQL?

Thanks!

Answer 1

Instead of relying on string processing, you can handle this via DateTime properties supported in LINQ to SQL:

var results = query.Select(o => o.StartTime.Date).Distinct();

If you want to view this as a string, later, you can use LINQ to Objects to convert the results:

var stringResults = results.AsEnumerable().Select(d => d.ToString("yyyy-MM-dd"));

Answer 2

I would do:

query.Select(o => o.StartTime.Date)
     .Distinct() // Runs on the database
     .AsEnumerable() // Convert to local sequence
     .Select(date => date.ToString("yyyy-MM-dd")) // Runs on the client

The DateTime.Date property (strips the sub-date portion out) is supported in LINQ to SQL, so everything other than the date-formatting will run on the database.