February 26, 2010 at 2:09 pm
I am trying to convert a DATETIME field to a DATE field in a SQL View. Using T-SQL in a query the following works:
convert(date,convert(char(11),[field name]))
When I type that into a view, I get a SQL Execution Error stating "Cannot call methods on date.". I need this in a view not a query.
Anyone know of a way to get this to work in a view?
February 26, 2010 at 2:31 pm
I tried this and it works perfect. What is the exact error you are having? See if this works.
create view vw_temp
as
select convert(date,convert(char(11),[changedate])) as Dateonly from sqlwatch.dbo.SW_ChangedObjects
select * from vw_temp
February 26, 2010 at 2:48 pm
When I go to the design of the view and click execute i get the following error:
SQL Execution Error
Executed SQL Statement: SELECT CONVERT (date, CONVERT(char(11),tdate)).ToString() as Dateonly FROM dbo.v_all_tracking_table
Error Source: .Net SqlClient Data Provider
Error Message: Cannot call methods on date.
However, when I create a new query:
SELECT TOP 1000 [Dateonly]
FROM [a_001].[dbo].[vw_temp]
It works perfectly. Any ideas on why you can't execute the view from the Design screen?
March 4, 2010 at 12:38 pm
try this:
SELECT convert(varchar(10),[yourdatecolumn],121) as Dateonly FROM dbo.v_all_tracking_table
March 5, 2010 at 1:00 am
You can also use the CAST statement since you wanted a DATE data type...not VARCHAR with an implicit conversion
cast(<datetime column> as date) as DateOnly
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply