guys.. i needd help in converting varchar to int

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 2000 Forums

SQL Server Development (2000)

guys.. i needd help in converting varchar to int

Author

Topic

twinklestar0802
Starting Member

17 Posts

Posted - 2005-05-30 : 13:23:33

this is mt query:
select c.customerid, c.customername, i.invoicenumber, i.amount, i.invoicedate,
i.term,
DATEADD(day, i.term, i.invoicedate) as duedate,
(amount-creditmemo) as 'invoicetotal', ((i.amount-i.creditmemo)- i.amountpaid)
as balance,
'aging'=case when (DATEDIFF(day,DATEADD(day, i.term, i.invoicedate),getdate()) )<0
then 0 else DATEDIFF(day,DATEADD(day, i.term, i.invoicedate),getdate()) end

from customers c left outer join invoice i
on i.customerid=c.customerid
where (i.amount-i.creditmemo)>i.amountpaid and i.void=0 and convert(int,'aging')>0

..i don't know how to convert from varchar to int.. it displays an error of "Syntax error converting the varchar value 'aging' to a column of data type int." why??? tnx guys

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-05-30 : 15:23:41

Hi
Your code is asking SQL Server to convert the string 'aging' to an INT, as you can't refer to an alias like that. Replace "'aging'" with the full case statement.

Mark

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-30 : 22:28:52

or
select * from
(
select c.customerid, c.customername, i.invoicenumber, i.amount, i.invoicedate,
i.term,
DATEADD(day, i.term, i.invoicedate) as duedate,
(amount-creditmemo) as [invoicetotal], ((i.amount-i.creditmemo)- i.amountpaid)
as balance,
[aging]=case when (DATEDIFF(day,DATEADD(day, i.term, i.invoicedate),getdate()) )<0
then 0 else DATEDIFF(day,DATEADD(day, i.term, i.invoicedate),getdate()) end
where (i.amount-i.creditmemo)>i.amountpaid) a
where convert(int,[aging])>0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-06-01 : 01:07:07

I beleive,the above script will still throw the same error as "[aging]=case when (DATEDIFF(day,DATEADD(day, i.term, i.invoicedate),getdate()) )<0
then 0 else DATEDIFF(day,DATEADD(day, i.term, i.invoicedate),getdate()) end
" as the datatype of aging is datetime.
Just try the same in the where clause by not converting the datatype but using aging <>0.
Hope that it solves the error problem

Thanks,
Vivek

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources