‘An expression of non-boolean type specified in a context where a condition is expected, near ‘,’.’ when date between

I am creating a sql query and I want to make profit and loss report but I’ve got this error in my query:

‘An expression of non-boolean type specified in a context where a condition is expected, near ‘,’.’

da = new SqlDataAdapter(
"SELECT
    CAST(Pay_invoice_details.Barcode AS INT), 
    SUM(CAST(Pay_invoice_details.Total_items_price AS INT)) AS 'Total Payes',
    SUM(CAST(Pay_invoice_details.Quantity AS INT) * 
    CAST(Buy_invoice_details.Purchase_price AS INT)) AS 'Total Purchases',
    CAST(Pay_invoice_details.Invoice_date AS DATE), 
    CAST(Buy_invoice_details.Barcode AS INT), 
    CAST(Buy_invoice_details.Invoice_date AS DATE), 
    SUM(CAST(Payouts_details.Total_amount AS INT)) AS 'Total Payouts' 
FROM 
    Pay_invoice_details,Buy_invoice_details,
    Payouts_details 
where
    Pay_invoice_details.Barcode = Buy_invoice_details.Barcode and 
    Pay_invoice_details.Invoice_date,
    Buy_invoice_details.Invoice_date,
    Payouts_details.Invoice_date between '" + from.Value.ToString("yyyy-MM-dd") + "' and'" + to.Value.ToString("yyyy-MM-dd") + "'",
sqlcon);

How can I fix this?

Answer

Just by identing your SQL command, it’s possible to see that there’s something wrong in it, look:

SELECT 
  CAST(Pay_invoice_details.Barcode AS INT), 
  SUM(CAST(Pay_invoice_details.Total_items_price AS INT)) AS 'Total Payes', 
  SUM(CAST(Pay_invoice_details.Quantity AS INT) * CAST(Buy_invoice_details.Purchase_price AS INT)) AS 'Total Purchases', 
  CAST(Pay_invoice_details.Invoice_date AS DATE), 
  CAST(Buy_invoice_details.Barcode AS INT), 
  CAST(Buy_invoice_details.Invoice_date AS DATE), 
  SUM(CAST(Payouts_details.Total_amount AS INT)) AS 'Total Payouts' 
FROM 
  Pay_invoice_details,
  Buy_invoice_details,
  Payouts_details 
where 
  Pay_invoice_details.Barcode=Buy_invoice_details.Barcode and 
  Pay_invoice_details.Invoice_date, 
  Buy_invoice_details.Invoice_date, 
  Payouts_details.Invoice_date 
    between '" + from.Value.ToString("yyyy-MM-dd") + "' and'" + to.Value.ToString("yyyy-MM-dd") + "'

On the WHERE clause you are using a list of 3 columns to compare to the date range using BETWEEN. That’s not how it works. If your intention is to consider the 1st column and if it is null, consider the second and if it is also null, consider the 3rd, you should use the COALESCE function. Like that:

(...)
where 
  Pay_invoice_details.Barcode=Buy_invoice_details.Barcode and 
  COALESCE(
    Pay_invoice_details.Invoice_date, 
    Buy_invoice_details.Invoice_date, 
    Payouts_details.Invoice_date
  ) between '" + from.Value.ToString("yyyy-MM-dd") + "' and'" + to.Value.ToString("yyyy-MM-dd") + "'