SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Cast in a view
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hockeyman9474
Starting Member

USA
11 Posts

Posted - 06/16/2014 :  09:29:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 06/16/2014 :  09:38:05  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 06/16/2014 :  09:38:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)



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

hockeyman9474
Starting Member

USA
11 Posts

Posted - 06/16/2014 :  09:58:54  Show Profile  Reply with Quote
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

USA
11 Posts

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 06/16/2014 :  10:38:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/16/2014 :  11:38:33  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000