[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

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()));


0
zimmer9
Asked:
zimmer9
  • 4
2 Solutions
 
dbeneitCommented:
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
 
ColosseoCommented:
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
 
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now