pls help me query base crystal report

See more:

VB

SQL

pls help me query base crystal report .... when i run below query in SQl i get in problem like conversion failed when converting date and/or time from character string in sql server 2008 useing/via crystal report Add command ..........................Thanks in advance for your co-operation.................

SELECT 
ACCOUNTNAME, 
SUM(TOTALDEPOSITEDAMOUNT) TOTALDEPOSITEDAMOUNT,
SUM(TOTALLOANPAIDAMOUNT) TOTALLOANPAIDAMOUNT,
SUM(TOTALPROFITPAIDAMOUNT) TOTALPROFITPAIDAMOUNT, 
SUM( TOTALEXPENSESAMOUNT) TOTALEXPENSESAMOUNT, 
SUM( TOTALOTHERSAMOUNT) TOTALOTHERSAMOUNT,
SUM(TOTALADMISSIONAMOUNT) TOTALADMISSIONAMOUNT, 
SUM(TOTALSERVICECHARGE) TOTALSERVICECHARGE, 
SUM(TOTALRISKABLESERVICECHARGE) TOTALRISKABLESERVICECHARGE, 
SUM(TOTALSERVICECHARGE) TOTALSERVICECHARGEs, 
SUM(TOTALDONEAMOUNT) TOTALDONEAMOUNT 
FROM
(SELECT 
Deposit.AcountName AS ACCOUNTNAME, 
SUM(Deposit.AmountDeposit) AS TOTALDEPOSITEDAMOUNT,
0 AS TOTALLOANPAIDAMOUNT,
0 AS TOTALPROFITPAIDAMOUNT, 
0 AS TOTALEXPENSESAMOUNT, 
0 AS TOTALOTHERSAMOUNT,
0 AS TOTALADMISSIONAMOUNT, 
0 AS TOTALSERVICECHARGE, 
0 AS TOTALRISKABLESERVICECHARGE, 
0 AS TOTALSERVICECHARGEs, 
0 AS TOTALDONEAMOUNT, 
Deposit.Ddate AS TRANDATE 
FROM Deposit GROUP BY Deposit.AcountName, Deposit.Ddate
UNION ALL
SELECT 
LoanPaid.AcountName AS ACCOUNTNAME, 
0 AS TOTALDEPOSITEDAMOUNT, 
SUM(LoanPaid.LPA)AS TOTALLOANPAIDAMOUNT, 
SUM(LoanPaid.PPA)AS TOTALPROFITPAIDAMOUNT, 
0 AS TOTALEXPENSESAMOUNT, 
0 AS TOTALOTHERSAMOUNT,
0 AS TOTALADMISSIONAMOUNT, 
0 AS TOTALSERVICECHARGE, 
0 AS TOTALRISKABLESERVICECHARGE, 
0 AS TOTALSERVICECHARGEs, 
0 AS TOTALDONEAMOUNT, 
LoanPaid.Ddate AS TRANDATE 
FROM LoanPaid GROUP BY LoanPaid.AcountName, LoanPaid.Ddate
UNION ALL
SELECT 
'NA' AS ACCOUNTNAME, 
0 AS TOTALDEPOSITEDAMOUNT,
0 AS TOTALLOANPAIDAMOUNT,
0 AS TOTALPROFITPAIDAMOUNT, 
SUM(Expenses.Amount)AS TOTALEXPENSESAMOUNT, 
0 AS TOTALOTHERSAMOUNT,
0 AS TOTALADMISSIONAMOUNT, 
0 AS TOTALSERVICECHARGE, 
0 AS TOTALRISKABLESERVICECHARGE, 
0 AS TOTALSERVICECHARGEs, 
0 AS TOTALDONEAMOUNT, 
Expenses.Ddate AS TRANDATE 
FROM Expenses GROUP BY Expenses.Ddate
UNION ALL 
SELECT 
'NA' AS ACCOUNTNAME, 
0 AS TOTALDEPOSITEDAMOUNT,
0 AS TOTALLOANPAIDAMOUNT,
0 AS TOTALPROFITPAIDAMOUNT, 
0 AS TOTALEXPENSESAMOUNT, 
SUM(Others.Amount) AS TOTALOTHERSAMOUNT, 
0 AS TOTALADMISSIONAMOUNT, 
0 AS TOTALSERVICECHARGE, 
0 AS TOTALRISKABLESERVICECHARGE, 
0 AS TOTALSERVICECHARGEs, 
0 AS TOTALDONEAMOUNT, 
Others.Ddate AS TRANDATE FROM Others
GROUP BY Others.Ddate
UNION ALL
SELECT 
AdminFee.AccountName AS ACCOUNTNAME, 
0 AS TOTALDEPOSITEDAMOUNT,
0 AS TOTALLOANPAIDAMOUNT,
0 AS TOTALPROFITPAIDAMOUNT, 
0 AS TOTALEXPENSESAMOUNT, 
0 AS TOTALOTHERSAMOUNT, 
SUM(AdminFee.AdminFee)AS TOTALADMISSIONAMOUNT, 
0 AS TOTALSERVICECHARGE, 
0 AS TOTALRISKABLESERVICECHARGE, 
0 AS TOTALSERVICECHARGEs, 
0 AS TOTALDONEAMOUNT, 
AdminFee.AdminDate AS TRANDATE 
FROM AdminFee GROUP BY AdminFee.AccountName, AdminFee.AdminDate
UNION ALL
SELECT 
LPB.AcountName AS ACCOUNTNAME, 
0 AS TOTALDEPOSITEDAMOUNT,
0 AS TOTALLOANPAIDAMOUNT,
0 AS TOTALPROFITPAIDAMOUNT, 
0 AS TOTALEXPENSESAMOUNT, 
0 AS TOTALOTHERSAMOUNT, 
0 AS TOTALADMISSIONAMOUNT, 
SUM(LPB.ServiceCharge)AS TOTALSERVICECHARGE, 
SUM (LPB.RiskAbleServiceCharge)AS TOTALRISKABLESERVICECHARGE, 
0 AS TOTALSERVICECHARGEs, 
0 AS TOTALDONEAMOUNT, 
LPB.Ddate AS TRANDATE 
FROM LPB GROUP BY LPB.AcountName, LPB.Ddate
UNION ALL
SELECT 
OPB.AcountName AS ACCOUNTNAME, 
0 AS TOTALDEPOSITEDAMOUNT,
0 AS TOTALLOANPAIDAMOUNT,
0 AS TOTALPROFITPAIDAMOUNT, 
0 AS TOTALEXPENSESAMOUNT, 
0 AS TOTALOTHERSAMOUNT, 
0 AS TOTALADMISSIONAMOUNT, 
0 AS TOTALSERVICECHARGE, 
0 AS TOTALRISKABLESERVICECHARGE, 
SUM(OPB.ServiceCharge)AS TOTALSERVICECHARGEs, 
0 AS TOTALDONEAMOUNT, 
OPB.Ddate  AS TRANDATE 
FROM OPB GROUP BY OPB.AcountName, OPB.Ddate
UNION ALL
SELECT 
Loan.AcountName AS ACCOUNTNAME, 
0 AS TOTALDEPOSITEDAMOUNT,
0 AS TOTALLOANPAIDAMOUNT,
0 AS TOTALPROFITPAIDAMOUNT, 
0 AS TOTALEXPENSESAMOUNT, 
0 AS TOTALOTHERSAMOUNT, 
0 AS TOTALADMISSIONAMOUNT, 
0 AS TOTALSERVICECHARGE, 
0 AS TOTALRISKABLESERVICECHARGE, 
0 AS TOTALSERVICECHARGEs,
SUM(Loan.LoanAmount)AS TOTALDONEAMOUNT, 
Loan.Ddate AS TRANDATE  
FROM Loan GROUP BY Loan.AcountName, Loan.Ddate) LONGQUERY
WHERE TRANDATE BETWEEN convert(smalldatetime,'" & dtpBegDate.Text & "' ,103)  AND  convert(smalldatetime,'" & dtpEndDate.text &"',103)
GROUP BY ACCOUNTNAME