Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Cast in a view

Author  Topic 

hockeyman9474
Starting Member

11 Posts

Posted - 2014-06-16 : 09:29:57
I'm trying to use Cast in a view but it automatically adds Top 100 percent and still wont work in a view. I get the following error:

SQL Select Execution Error
Executed SQL Statement: Select Top (100) Percent CAST[(Sales Accepted Date]AS DATE). ToString() AS SAC , Count(Cast([Sale Accepted Date]AS DATE) AS [Sales Count] FROM dbo.SdcYtdSales GROUP BY CAST([Sale Accepted Date] AS DATE) ORDER BY SAC
Error Source: .Net SqlClient Data Provider
Error Message: Cannot Call Methods on date



1. Also here is my code from the query:

SELECT CAST([Sale Accepted Date] as DATE) as SAC,count(CAST([Sale Accepted Date] as DATE))
from SdcYtdSales
group by CAST([Sale Accepted Date] as DATE)
order by SAC asc

2. and my code from the view including the Top statement:

SELECT TOP (100) PERCENT CAST([Sale Accepted Date] AS DATE) AS SAC, COUNT(CAST([Sale Accepted Date] AS DATE)) AS Expr1
FROM dbo.SdcYtdSales
GROUP BY CAST([Sale Accepted Date] AS DATE)
ORDER BY SAC

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-16 : 09:38:05
quote:
Originally posted by hockeyman9474

I'm trying to use Cast in a view but it automatically adds Top 100 percent and still wont work in a view. I get the following error:

SQL Select Execution Error
Executed SQL Statement: Select Top (100) Percent CAST[(Sales Accepted Date]AS DATE). ToString() AS SAC , Count(Cast([Sale Accepted Date]AS DATE) AS [Sales Count] FROM dbo.SdcYtdSales GROUP BY CAST([Sale Accepted Date] AS DATE) ORDER BY SAC
Error Source: .Net SqlClient Data Provider
Error Message: Cannot Call Methods on date



1. Also here is my code from the query:

SELECT CAST([Sale Accepted Date] as DATE) as SAC,count(CAST([Sale Accepted Date] as DATE))
from SdcYtdSales
group by CAST([Sale Accepted Date] as DATE)
order by SAC asc

2. and my code from the view including the Top statement:

SELECT TOP (100) PERCENT CAST([Sale Accepted Date] AS DATE) AS SAC, COUNT(CAST([Sale Accepted Date] AS DATE)) AS Expr1
FROM dbo.SdcYtdSales
GROUP BY CAST([Sale Accepted Date] AS DATE)
ORDER BY SAC


There are a few syntax errors in your first query. That may be the reason rather than use of CAST. Try this:
 Select Top (100) Percent CAST([Sales Accepted Date] AS DATE).ToString() AS SAC , 
Count(Cast([Sale Accepted Date]AS DATE)) AS [Sales Count] FROM dbo.SdcYtdSales
GROUP BY CAST([Sale Accepted Date] AS DATE) ORDER BY SAC
Also, not sure at the purpose of TOP (100) percent is. There was a hack that people used in old versions of SQL to get data from a view ordered in a particular manner where they used TOP (100) percent, but that does not work in sQL 2012.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-16 : 09:38:25
[code]SELECT CONVERT(VARCHAR(30), [Sales Accepted Date], 101) AS SAC,
COUNT([Sale Accepted Date]) AS [Sales Count]
FROM dbo.SdcYtdSales
GROUP BY CONVERT(VARCHAR(30), [Sales Accepted Date], 101)
ORDER BY CONVERT(VARCHAR(30), [Sales Accepted Date], 101)[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

hockeyman9474
Starting Member

11 Posts

Posted - 2014-06-16 : 09:58:54
SQL keeps adding top 100 percent by itself. But you suggest to use convert as opposed to cast. I have been trying to research the two. What's the difference?
Go to Top of Page

hockeyman9474
Starting Member

11 Posts

Posted - 2014-06-16 : 10:00:25
also please explain the 101. what does that signify?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-16 : 10:38:15
According to Books Online (the SQL Server help file) it denotes the final format of the string representative value of the datetime data.
The 101 format parameter says the string output from the datetime data is formatted as "mm/dd/yyyy".





Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-16 : 11:38:33
Don't put an ORDER BY clause in a view. It doesn't make sense and SQL will ignore it anyway. Plus, that could be the issue with your query.
Go to Top of Page
   

- Advertisement -