title: How do I get EntityFrameworkCore generated SQL to use the right format for DateTime objects?
body: <p>I'm using <a href="https://www.nuget.org/packages/Microsoft.EntityFrameworkCore/2.1.2" rel="noreferrer">Microsoft.EntityFrameworkCore.SqlServer v2.1.2</a> (and have also tried v2.2.0-preview1-35029) and am using LINQ to fetch a collection of entities from an Azure SqlServer database, filtering on a DateTime field.  </p>
  
  <p>However, the SQL generated by the LINQ statement uses a string-based DateTime value that SqlServer rejects with this error:</p>
  
  <blockquote>
    <p>Conversion failed when converting date and/or time from character string.</p>
  </blockquote>
  
  <p>I can modify the SQL statement to change the datetime format so that the query works without error (see below for details), but I don't know how to get the framework to generate that same datetime format.</p>
  
  <p>While EntityFrameworkCore is still kinda new, it seems like this is a pretty straightforward use case, so I'm assuming I'm doing something wrong and that it's not a framework issue.  </p>
  
  <p><strong>How do I prevent EF from generating an invalid datetime value in the SQL?</strong> </p>
  
  <p>and/or</p>
  
  <p><strong>How do I get the generated SQL to use another format for DateTime objects?</strong></p>
  
  <hr>
  
  <p>The EntityFramework model I'm using looks like this:</p>
  
  <pre><code>public class DeskReading
  {
      public int DeskReadingId { get; set; }
      //... some other fields ...
      public DateTime Timestamp { get; set; }
  }
  </code></pre>
  
  <p>And my LINQ to query the values looks like this:</p>
  
  <pre><code>IQueryable&lt;DeskReading&gt; readings = 
      _dbContext.DeskReadings
              .OrderBy(gr =&gt; gr.Timestamp)
              .Where(gr =&gt; gr.Timestamp &gt; new DateTime(2017, 05, 01));
  
  readings.ToList();
  </code></pre>
  
  <p>And the SQL that's generated by that looks like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT [gr].[DeskReadingId] --...some other fields ...
  FROM [DeskReadings] AS [gr]
  WHERE [gr].[Timestamp] &gt; '2017-05-01T00:00:00.0000000'
  ORDER BY [gr].[Timestamp]
  </code></pre>
  
  <p>Note that the value for the filter is <code>'2017-05-01T00:00:00.0000000'</code></p>
  
  <p>If I run that SQL directly on the SqlServer via SSMS, I get the same error:</p>
  
  <p><a href="https://i.stack.imgur.com/gOZry.png" rel="noreferrer"><img src="https://i.stack.imgur.com/gOZry.png" alt="enter image description here"></a></p>
  
  <p>But if I change the filter to use <code>'2017-05-01 00:00:00'</code>, it works fine:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT [gr].[DeskReadingId] --...some other fields ...
  FROM [DeskReadings] AS [gr]
  WHERE [gr].[Timestamp] &gt; '2017-05-01 00:00:00'
  ORDER BY [gr].[Timestamp]
  </code></pre>
  
  <p>As requested, here's the create script for the table:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE [dbo].[DeskReadings](
      [DeskReadingId] [int] IDENTITY(1,1) NOT NULL,
      [SoilMoistureSensor1] [int] NOT NULL,
      [SoilMoistureSensor2] [int] NOT NULL,
      [LightLevel] [int] NOT NULL,
      [TemperatureF] [real] NOT NULL,
      [HumidityPercent] [real] NOT NULL,
      [Timestamp] [datetime] NOT NULL,
   CONSTRAINT [PK_dbo.DeskReadings] PRIMARY KEY CLUSTERED 
  (
      [DeskReadingId] ASC
  )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>