SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Conversion failed when converting the varchar
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dwg23
Starting Member

2 Posts

Posted - 05/29/2013 :  15:23:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 05/29/2013 :  15:49:42  Show Profile  Reply with Quote
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
Go to Top of Page

dwg23
Starting Member

2 Posts

Posted - 05/29/2013 :  16:32:28  Show Profile  Reply with Quote
Thanks!
I see how it works now and will definitely check out the link.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 05/29/2013 :  17:35:05  Show Profile  Reply with Quote
You are very welcome - glad to help
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000