exsasan
asked on
Use Cast or Convert to get Date part in a View Error
Hi there,
I try to get the Date part of a Date-Time field and show it in a view, I tried both Cast and Convert and both works fine in a regular Select statement like:
SELECT CAST(LOG_DATE AS date) AS Expr1
FROM TABLE1
But when I try the same query inside a View I get Error for both cast and Convert as:
"Can not call methods on date."
My resalut should be a clean date part without any extra 00:00:00 , and can be just a string(Dosn't need to be date format) , also shouldn't be very costly because I have hundered of thousends of records,
I use SQL server 2008.
Thanks
I try to get the Date part of a Date-Time field and show it in a view, I tried both Cast and Convert and both works fine in a regular Select statement like:
SELECT CAST(LOG_DATE AS date) AS Expr1
FROM TABLE1
But when I try the same query inside a View I get Error for both cast and Convert as:
"Can not call methods on date."
My resalut should be a clean date part without any extra 00:00:00 , and can be just a string(Dosn't need to be date format) , also shouldn't be very costly because I have hundered of thousends of records,
I use SQL server 2008.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
strickdd
http://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm
ASKER
It worked, I was missing the second SELECT, Thanks a lot
Really? Because what I posted is syntactically inccorrect - it should be only one SELECT.
ASKER
This is the way it worked: (I'm using Management Studio,and create new View and inside):
SELECT Column1, (SELECT CAST(Col_DateTime AS date) AS Expr1) AS [New Column Name]
From .....
If I try to use Cast or Convert directly, without the Select It give me the error that I mentioned,
Why? is it a miracle ? :)
SELECT Column1, (SELECT CAST(Col_DateTime AS date) AS Expr1) AS [New Column Name]
From .....
If I try to use Cast or Convert directly, without the Select It give me the error that I mentioned,
Why? is it a miracle ? :)
That is a bug in the Query Designer. In a New Query window you can execute
create view tst as select column1, cast(col_datetime as date) as [New Column Name] from ...
Obviously the Query Designer tries to apply the .NET method ToString() to the cast/convert, as soon as there is "date" involved.
create view tst as select column1, cast(col_datetime as date) as [New Column Name] from ...
Obviously the Query Designer tries to apply the .NET method ToString() to the cast/convert, as soon as there is "date" involved.
mark_wills located an MS Connect entry regarding this issue:
http://connect.microsoft.com/SQLServer/feedback/details/587490/cannot-call-methods-on-date
That is, MS is aware, and there will most likely be no patch (neither for 2008 nor 2008 R2).
http://connect.microsoft.com/SQLServer/feedback/details/587490/cannot-call-methods-on-date
That is, MS is aware, and there will most likely be no patch (neither for 2008 nor 2008 R2).
ASKER
That's right, Thanks for your followups,