Syntax error on SQL Server Using Query Analyzer

I get the error message:

Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near ']'.

This line 9 is as follows (and the entire SQL code follows):
((DateDiff(M,[DateLost],DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) < MthTo])))

I have tried to convert an Access query object's SQL code into Transact SQL to try and run in a Stored Procedure:

Converted code is as follows followed by the original code before the conversion:

SELECT tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
Sum(isnull(MarketValue,0)+isnull(CashBalance,0)) AS AcctValue,
DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()) AS DFrom,
DATEADD(M, [MthTo] * -1, DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE()))
AS DateFromC, tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE())) AS DateToC
FROM tblMthRanges, tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber=tblProducts.CustomerNumber
WHERE (((DATEDIFF(M, [DateLost], DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) >= [MthFrom]) AND
((DateDiff(M,[DateLost],DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) < MthTo])))

GROUP BY tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()), tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE()),
DATEADD(M,[MthTo]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()));
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
My original code in a query object in Access follows and I attempted to convert it into Transact SQL which I listed above.

Original code in Access query object:

SELECT tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
Sum(Nz(MarketValue,0)+Nz(CashBalance,0)) AS AcctValue,
Date()-DatePart("d",Date()) AS DFrom,
DateAdd("m",[MthTo]*-1,Date()-DatePart("d",Date()-1))
AS DateFromC, tblMthRanges.MthTo,
DateAdd("m",[MthFrom]*-1,Date()-DatePart("d",Date())) AS DateToC
FROM tblMthRanges, tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber=tblProducts.CustomerNumber
WHERE (((DateDiff("m",[DateLost],Date()-DatePart("d",Date()-1)))>=[MthFrom]) AND
((DateDiff("m",[DateLost],Date()-DatePart("d",Date()-1)))<[MthTo]))

GROUP BY tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
Date()-DatePart("d",Date()), tblMthRanges.MthTo,
DateAdd("m",[MthFrom]*-1,Date()-DatePart("d",Date()-1)),
DateAdd("m",[MthTo]*-1,Date()-DatePart("d",Date()));


zimmer9Asked:
Who is Participating?
 
ColosseoConnect With a Mentor Commented:
Hi

at first glance the problem with this line:

((DateDiff(M,[DateLost],DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) < MthTo])))

is that you are missing the opening [ around MthTo

try this line

((DateDiff(M,[DateLost],DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) < [MthTo])))

HTH

Scott
0
 
dbeneitConnect With a Mentor Commented:
Please, test this

SELECT tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
Sum(isnull(MarketValue,0)+isnull(CashBalance,0)) AS AcctValue,
DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()) AS DFrom,
DATEADD(M, [MthTo] * -1, DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE()))
AS DateFromC, tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE())) AS DateToC
FROM tblMthRanges, tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber=tblProducts.CustomerNumber
WHERE (((DATEDIFF(M, [DateLost], DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) >= [MthFrom]) AND
((DateDiff(M,[DateLost],DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) < [MthTo])))

GROUP BY tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()), tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE()),
DATEADD(M,[MthTo]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()));
0
 
zimmer9Author Commented:
Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'GROUP'.

Line 11 is as follows:

GROUP BY tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,


SELECT tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
Sum(isnull(MarketValue,0)+isnull(CashBalance,0)) AS AcctValue,
DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()) AS DFrom,
DATEADD(M, [MthTo] * -1, DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE()))
AS DateFromC, tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE())) AS DateToC
FROM tblMthRanges, tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber=tblProducts.CustomerNumber
WHERE (((DATEDIFF(M, [DateLost], DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) >= [MthFrom]) AND
((DateDiff(M,[DateLost],DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) < [MthTo])))

GROUP BY tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()), tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE()),
DATEADD(M,[MthTo]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()));
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
zimmer9Author Commented:
It appears that in the following WHERE clause I have 17 openinig parens and only 16 closing parens

WHERE (((DATEDIFF(M, [DateLost], DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) >= [MthFrom]) AND
((DateDiff(M,[DateLost],DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) < [MthTo])))
0
 
zimmer9Author Commented:
It appears that in the following GROUP BY clause I have 15 open parens and 14 closing parens:


GROUP BY tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()), tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE()),
DATEADD(M,[MthTo]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()));
0
 
zimmer9Author Commented:
The following seems to work:

WHERE (((DATEDIFF(M, [DateLost], DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) >= [MthFrom])) AND
((DateDiff(M,[DateLost],DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) < [MthTo])))

GROUP BY tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()), tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE()),
DATEADD(M,[MthTo]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE());
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.