hello
i am trying to move from Access db to Sql.s
could you please what is rwong in the sql.s syntax:
Access SP:-
PARAMETERS [@.Date_M_Y] DateTime, [@.BIZID] Long;
SELECT SUM(MINUTE(Time_)) AS MM, SUM(HOUR(Time_)) AS HH, COUNT(Date_) AS SHIFTS, SUM(Money_Sum) AS MONEY_TOTAL, AVG(Money_Sum) AS AVG_MONEY_PER_SHIFT
FROM TblTimeSum
WHERE MONTH(Date_)=MONTH([@.Date_M_Y]) And YEAR(Date_)=YEAR([@.Date_M_Y]) And BIZID=[@.BIZID];
-- SQL.S-
CREATE PROCEDURE SP_SUM
@.Date_M_Y smalldatetime,
@.BIZID int
AS
BEGIN
SELECT SUM(MINUTE(Time_)) AS MM, SUM(HOUR(Time_)) AS HH, COUNT(Date_) AS SHIFTS, SUM(Money_Sum) AS MONEY_TOTAL, AVG(Money_Sum) AS AVG_MONEY_PER_SHIFT
FROM TblTimeSum
WHERE MONTH(Date_)=MONTH([@.Date_M_Y]) And YEAR(Date_)=YEAR([@.Date_M_Y]) And BIZID=[@.BIZID]
END
GO
thanks
I don't believe that T-SQL has a Minute function. Try using the DATEPART function in place of the MINUTE function.
See: http://msdn2.microsoft.com/en-us/library/ms174420.aspx
|||
I think that this has been answered in the Transact-SQL forum.
Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).
|||
 Arnie Rowland wrote: |
| |
| I think that this has been answered in the Transact-SQL forum. Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere). |
Yes this is very tasteful, welcoming, and polite. It directly answers the original question and promotes deep thought. A good example of the quality found within. If each of us posted this rude comment, the world would be a better place to live but I guess that's how MVP status is obtained.
Let's pass the love forward.
Very nice Arnie. Keep it up.
|||
Yes the date and time functions need to be acheived through a CONVERT() format.
Adamus
