0

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'.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
fadoewae
  • 11
  • 3
  • `= (expression) b.TransactionDate` <-- exactly what did you miss after the parenthesis here before the `b`? Or... exactly what did you forget to *remove* after you changed the sql? – Lasse V. Karlsen Jan 21 '17 at 18:33

1 Answers1

2

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
Lasse V. Karlsen
  • 380,855
  • 102
  • 628
  • 825
  • @fadoewae: If an answer solved your problem you should accept it so that other people will know that the problem is solved. – Zohar Peled Jan 22 '17 at 07:08