How do I get the date from a datetime when creating a SQL view?

Question

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.

Version: SQL Server v17.0

Example:
field in the table is: InvoiceDate(datetime,null)
data is: 2016-11-15 00:00:00.000

my SQL code is:

CONVERT(date,ihhd.InvoiceDate,101) AS InvoiceDate

my InvoiceDate result is: 2016-11-15

when I put that same code into a view I get this:

SQL Execution Error.
Executed SQL SELECT [selected fields]
Error Source: .Net SqlClient Data Provider
Error Message: Cannot call methods on date.

I've tried to convert it to a varchar:

CONVERT(varchar,ihhd.InvoiceDate,101) AS InvoiceDate

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.

I also tried do double convert it:

CONVERT(date,CONVERT(varchar,ihhd.InvoiceDate,101),101) AS InvoiceDate

again the SQL window was OK with it and the view window threw up the same error.

What am I doing wrong?

Answer 1

Your are converting a DATETIME to a DATE so it will produce the expected yyyy-mm-dd. You need to convert to a string if you want MM/DD/YYYY. Keep in mind this converted string is NOT a date, and should really be relegated to the presentation layer.

Select AsString = convert(varchar(10),GetDate(),101)  -- notice the varchar(10)
      ,AsDate   = convert(date,GetDate(),101)

Returns

AsString    AsDate
06/14/2017  2017-06-14

---

Conversely, you can take a MM/DD/YYYY string and convert to a date

Select convert(date,'06/14/2017',101)   -- If 2012+ try_convert()

Returns

2017-06-14