title: How do I get the date from a datetime when creating a SQL view?
body: <p>I have searched for hours and found a lot of information about how do convert a datetime field to a date. All of it works well in my SQL window. However, the minute I try to use in in a view it crashes.</p>
  
  <p>Version: SQL Server v17.0</p>
  
  <p>Example:<br>
  field in the table is: InvoiceDate(datetime,null)<br>
  data is: 2016-11-15 00:00:00.000  </p>
  
  <p>my SQL code is: </p>
  
  <pre><code>CONVERT(date,ihhd.InvoiceDate,101) AS InvoiceDate
  </code></pre>
  
  <p>my InvoiceDate result is: 2016-11-15</p>
  
  <p>when I put that same code into a view I get this:</p>
  
  <blockquote>
    <p>SQL Execution Error.<br>
    Executed SQL SELECT [selected fields]<br>
    Error Source: .Net SqlClient Data Provider<br>
    Error Message: Cannot call methods on date.</p>
  </blockquote>
  
  <p>I've tried to convert it to a varchar: </p>
  
  <pre><code>CONVERT(varchar,ihhd.InvoiceDate,101) AS InvoiceDate
  </code></pre>
  
  <p>that does not return the same error in the view window. However,the report writer that will use this data does not allow "date-like" comparisons so I need the field in a date format.</p>
  
  <p>I also tried do double convert it: </p>
  
  <pre><code>CONVERT(date,CONVERT(varchar,ihhd.InvoiceDate,101),101) AS InvoiceDate
  </code></pre>
  
  <p>again the SQL window was OK with it and the view window threw up the same error.</p>
  
  <p>What am I doing wrong?</p>