Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Forums » .NET » SQL Server »
Problem in extracting feilds crystal report
Posted Date: 10 Jan 2009 Posted By: pallavi satish punekar Member Level: Gold Points: 1
Responses:
1
|
i have a stored procedure where accepts the parameters
month and year
alter proc Cg_RPT_StoresMonthlyCost
(
@vch_month varchar(2),
@vch_year varchar(4)
)
as
declare @frmdate datetime,@todate datetime
set @frmdate=convert(varchar(10),@vch_year+'-'+@vch_month+'-'+'01')
set @todate=convert(varchar(10),dateadd(d,-1,dateadd(m,1,convertdatetime,@frmdate,120)))
select sum(b.issuedquantity*d.rate) as Totalcost,a.departmentcode,c.departmentname
from mrs a,mrsitem b,departmentmaster c,itemmaster d
where a.mrsid=b.mrsid and a.departmentcode=c.departmentcode and b.itemcode=d.itemcode
and convert(varchar(10),a.mrsdate,120) between @frmdate and @todate and a.departmentcode not in('7000','7001')
group by a.departmentcode,c.departmentname
When i do this it works fine in SQL .But in crystal report when i try to add the tables it wil nt show mw the feilds as wel as it gives me error as
'conversion failed when trying to convert from datetime to charcter string'
what is the problem.
|
Responses
|
| Author: jumbo 12 Jan 2009 | Member Level: Gold | Rating:  Points: 6 |
I read your question.
and i reach on the stage that you are doing date conversion in stored procedure like convert(varchar(10),a.mrsdate,120). ok.
Instead of that use convert(varchar(10),a.mrsdate,101). This will work for you.
----------------------------
i have a stored procedure where accepts the parameters
month and year
alter proc Cg_RPT_StoresMonthlyCost
(
@vch_month varchar(2),
@vch_year varchar(4)
)
as
declare @frmdate datetime,@todate datetime
set @frmdate=convert(varchar(10),@vch_year+'-'+@vch_month+'-'+'01')
set @todate=convert(varchar(10),dateadd(d,-1,dateadd(m,1,convertdatetime,@frmdate,120)))
select sum(b.issuedquantity*d.rate) as Totalcost,a.departmentcode,c.departmentname
from mrs a,mrsitem b,departmentmaster c,itemmaster d
where a.mrsid=b.mrsid and a.departmentcode=c.departmentcode and b.itemcode=d.itemcode
and convert(varchar(10),a.mrsdate,120) between @frmdate and @todate and a.departmentcode not in('7000','7001')
group by a.departmentcode,c.departmentname
When i do this it works fine in SQL .But in crystal report when i try to add the tables it wil nt show mw the feilds as wel as it gives me error as
'conversion failed when trying to convert from datetime to charcter string'
what is the problem.
|
| Post Reply |
|
|
|
 | | This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post. |
|
|
|
|