Why am I receiving "Conversion failed when converting date and/or time from character string."?

Question

I am wanting to total the days in the query for [# of orders] & [total revenue] when I run this query. But I am receiving this error in my sql server "Conversion failed when converting date and/or time from character string".

My code is this:

select  TaxDate [Date]
        ,count(docentry) [# of Orders]
        ,sum(doctotal)-(SUM(TotalExpns)+SUM(VatSum)) [$]

from ORDR

where   CANCELED<>'Y'
        and SlpCode = '37'
        and TaxDate >= '2015-05-26'
        and DocStatus = 'C'

GROUP BY TaxDate

UNION ALL

select  'Total'
        ,SUM(docentry)
        ,sum(doctotal)-(SUM(TotalExpns)+SUM(VatSum))

from ORDR

where   CANCELED<>'Y'
        and SlpCode = '37'
        and TaxDate >= '2015-05-26'
        and DocStatus = 'C'

group by TaxDate
order by TaxDate

I am very new to writing queries. I have no experience with using the "UNION" tool. So I appreciate any advice on this.

Answer 1

You have a union all with a date in the first subquery and 'Total' in the second. SQL Server decides that both should be dates, so you are getting a conversion error on 'Total'.

They need to be the same type. Try changing the first to something like:

select convert(varchar(10), TaxDate, 121) as [Date],
       count(docentry) [# of Orders],
       sum(doctotal)-(SUM(TotalExpns)+SUM(VatSum)) [$]

You don't need a union all for this query anyway. I think with rollup does what you want:

select  TaxDate [Date], count(docentry) [# of Orders],
        sum(doctotal)-(SUM(TotalExpns)+SUM(VatSum)) [$]
from ORDR
where CANCELED <> 'Y' and SlpCode = '37' and TaxDate >= '2015-05-26' and
      DocStatus = 'C'
group by TaxDate with rollup;