[SOLVED] Incorrect Syntax near 'b' and failed to convert date or time

Incorrect Syntax near 'b' and failed to convert date or time

I have 2 queries.

The first one got an error message that it failed to convert date or time

select a.ProductID, sum(a.Quantity) as Qty ,SUM (a.SubTotal) as Sub
from TransactionDetail a join Transactions b on a.TransactionID = b.TransactionDate
group by productID

So I tried to convert date and time as follows

select  a.ProductID, sum(a.Quantity) as Qty ,SUM (a.SubTotal) as Sub
from TransactionDetail a join Transactions b on a.TransactionID = (Convert(varchar(10),b.TransactionDate,101)) b.TransactionDate
group by productID

but now I got this error message:

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'b'.

Solution

This part:

 = (Convert(varchar(10),b.TransactionDate,101)) b.TransactionDate group by productID

is what the server is complaining about.

Specifically, here is the b the error message is referring to:

                                                v
 = (Convert(varchar(10),b.TransactionDate,101)) b.TransactionDate group by productID

Most likely you inserted the new parenthesized expression but forgot to remove the part you had there before. In other words, you likely want to simply remove this section:

                                                v---------------v
 = (Convert(varchar(10),b.TransactionDate,101)) b.TransactionDate group by productID