Convert Access query to SQL View

Hi,
I have used an access table to create a chart report to show how many calls
were handled in a month. How do i conver the Access sql to MS sql server
view. Here is the access sql:
SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
FROM [Complaints]
GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
(Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
In the MS Sql, the word FORMAT is not recognised. I have tried several ways
like this w/o any luck. Can anyone tell me the correct syntax for the above
sql querry.
Thanks
SenthilkumarCheck out CONVERT() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Senthilkumar" <kesk32@.yahoo.co.in> wrote in message
news:%234%232fJmJGHA.1388@.TK2MSFTNGP11.phx.gbl...
Hi,
I have used an access table to create a chart report to show how many calls
were handled in a month. How do i conver the Access sql to MS sql server
view. Here is the access sql:
SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
FROM [Complaints]
GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
(Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
In the MS Sql, the word FORMAT is not recognised. I have tried several ways
like this w/o any luck. Can anyone tell me the correct syntax for the above
sql querry.
Thanks
Senthilkumar|||To add to Tom's reply, you might want to consider leaving off the
formatting in the view and just return the raw data. Let the client
format the result set. Any time you format the data on the server, the
formatting functions you use have to operate on every single row, one
at a time.
--Mary
On Tue, 31 Jan 2006 18:09:30 +0530, "Senthilkumar"
<kesk32@.yahoo.co.in> wrote:

>Hi,
>I have used an access table to create a chart report to show how many calls
>were handled in a month. How do i conver the Access sql to MS sql server
>view. Here is the access sql:
>SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
>FROM [Complaints]
>GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
> (Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
>ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
>In the MS Sql, the word FORMAT is not recognised. I have tried several ways
>like this w/o any luck. Can anyone tell me the correct syntax for the above
>sql querry.
>Thanks
>Senthilkumar
>