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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy