CODEWITHSUNDEEP
sqlsql-serverdatedatetimesql-convert
C.Smith
C.Smith

Reputation: 31

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

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?

Upvotes: 3

Views: 10788

Answers (4)

Remco Spruit
Remco Spruit

Reputation: 1

In the View editor you cannot run it. Save it and use a query that uses the saved view and that works like a charm

Upvotes: 0

Mike Anthony
Mike Anthony

Reputation: 1

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.

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

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.

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 82000

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

Upvotes: 1

Related Questions