Conversion failed when converting the varchar

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Conversion failed when converting the varchar

Author

Topic

dwg23
Starting Member

2 Posts

Posted - 2013-05-29 : 15:23:29

Hello,
I am using the below code to pull customers that are over 40 days past due on their invoices and keep getting the "Conversion Failed when converting the varchar value 'OUT' to data type int" Message.

I have tried using convert but had no luck.

Any ideas?

SELECT tblarInvoice.InvoiceNumber
, tblarInvoice.Status
, tblarInvoice.Customer
, tblarInvoice.InvoiceToName
, tblarInvoice.InvoiceAmount
, tblarInvoice.PaidAmount
, tblarInvoice.InvoiceDate
, tblarInvoice.InvoiceDueDate
, datediff(dd,InvoiceDueDate, Getdate()) AS 'Out'
FROM
dbo.tblarInvoice
where Status = 'open' AND 'Out' > 40

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-29 : 15:49:42

T-SQL syntax does not allow you to use a column defined in the SELECT list (in your case, the 'Out' column) anywhere else in that query except in the ORDER BY clause. This is because of the logical sequence in which SQL Engine processes the query. If you are interested in logical query processing phases, see here.

So change your query to use the actual expression for 'Out' in the where clause as well

SELECT  tblarInvoice.InvoiceNumber ,
        tblarInvoice.Status ,
        tblarInvoice.Customer ,
        tblarInvoice.InvoiceToName ,
        tblarInvoice.InvoiceAmount ,
        tblarInvoice.PaidAmount ,
        tblarInvoice.InvoiceDate ,
        tblarInvoice.InvoiceDueDate ,
        DATEDIFF(dd, InvoiceDueDate, GETDATE()) AS 'Out'
FROM    dbo.tblarInvoice
WHERE   Status = 'open'
        AND DATEDIFF(dd, InvoiceDueDate, GETDATE()) > 40

dwg23
Starting Member

2 Posts

Posted - 2013-05-29 : 16:32:28

Thanks!
I see how it works now and will definitely check out the link.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-29 : 17:35:05

You are very welcome - glad to help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources