CODEWITHSUNDEEP
sqlt-sqlsapb1
KTate
KTate

Reputation: 57

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

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.

Upvotes: 1

Views: 4928

Answers (2)

Siyual
Siyual

Reputation: 16917

You are getting this error because in your second query of the UNION, your first column is explicitly a VARCHAR, but in your first one, it is a DATE. These datatypes need to match.

I would suggest doing the following:

select  Cast(TaxDate As Varchar (10)) [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 [Date]

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269533

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;

Upvotes: 5

Related Questions