3

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?

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
C.Smith
  • 31
  • 1
  • 2
  • here my entire select statement. It works on the SQL window but not when I paste it to a view window: SELECT ihhd.ARDivisionNo AS Div, ihhd.CustomerNo AS Acct, ihhd.CustomerName, ihhd.ShipToCode, ihhd.ShipToName, ihhd.Salesperson, ihhd.InvoiceNo AS Invoice#, convert(date,CONVERT(varchar,ihhd.InvoiceDate,101),101) AS InvoiceDate FROM vARInvoiceHistoryHeaderDetail ihhd WHERE LEFT(ihhd.InvoiceNo, 2) <> 'CL' – C.Smith Jun 14 '17 at 18:29
  • I wanted to send screen shots but can't figure out how, sorry. Thanks for helping. I'll continue to review the code. I didn't have this problem on my 2012 SQL server so it must be something I'm doing. – C.Smith Jun 14 '17 at 18:34

3 Answers3

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
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
0

Unreproducible.

I just tested this code on the datetime column in an existing table:

CREATE VIEW vDT
AS 
SELECT CONVERT(date, [StartTime],101) AS tDate
FROM [dbo].[Trace20150811];

I got no error, and was able to SELECT from the view afterwards and get expected results.

Go over your code more carefully, because the real reason you are getting the error is not in the code you posted. If you cannot find it, post your complete view code without changing anything.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
0

I've had this exact same problem for years. The code works fine in a query window, stored proc, etc but when I use it in a View it errors out. In a View, the CONVERT function works for other data types but it won't allow you to convert to Date type. If I CONVERT to VARCHAR(10) then it looks fine but if you use Crystal or Excel to retrieve the data it doesn't see that field as a Date type and therefore you can't do date filtering.

Mike Anthony
  • 1