Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
Hello every one ,
I am having problem in fetching data data from another Data base .
I am having 2 data base
 
1.BankingDB
2.MarketingDB
 
in this 2 DB i am having a same table MembersTB
i.e in banking i am having table MembersTB an also i am having table MembersTB in MarketingDB also with same column names .
 
Now i need to display the members record from StartDate to EndDate from both DataBase . I need to append both table from so i am writing query in banking table . For banking table select query is not a problem but i am facing problem in fetching data form another DB(MarketingDB).
 
Banking Query (Good)
 
SELECT    MemberShipTB.AppNo,  MemberShipTB.MemberID, MemberShipTB.Name, MemberShipTB.ReceiptNo,				((MemberShareTb.ShareAmt*MemberShareTb.No_of_Shares)+MemberShareTb.AdminAmt+MemberShareTb.EntranceAmt),MemberShipTB.DOJ		         	FROM AmulyaBankingMarket.dbo. MemberShipTB INNER JOIN AmulyaBankingMarket.dbo.
                 MemberShareTb ON AmulyaBankingMarket.dbo. MemberShipTB.MemberID = AmulyaBankingMarket.dbo.MemberShareTb.MemberID where   MemberShipTB.DOJ between '12/1/2012 12:00:00 am' and '2/19/2013 11:59:59 pm' 
 
 
since I need to fetch data from another table I need to write in this format
 

 
  declare @QueryExecution varchar(1000),@CrossDBname varchar(500),@StartDate Datetime,@EndDate datetime 
 

   set @StartDate='12/1/2012 12:00:00 am' 
  set @EndDate='2/19/2013 11:59:59 pm'
 

declare @QueryExecution varchar(500),@CrossDBname varchar(40)
   execute @CrossDBname='MarketingDB.dbo.'
 

set @QueryExecution= 'SELECT    MemberShipTB.AppNo,  MemberShipTB.MemberID, MemberShipTB.Name, MemberShipTB.ReceiptNo,((MemberShareTb.ShareAmt*MemberShareTb.No_of_Shares)+MemberShareTb.AdminAmt+MemberShareTb.EntranceAmt),MemberShipTB.DOJ       			FROM '+@CrossDBname+' MemberShipTB INNER JOIN '+@CrossDBname+'							 MemberShareTb ON '+@CrossDBname+' MemberShipTB.MemberID = '+@CrossDBname+'MemberShareTb.MemberID where (MemberShipTB.DOJ between '+@startDate+' and '+  @endDate+')'				
 execute(@QueryExecution)
 

 
Here i am specifying DB name in order to fetch data from other DB .
 
But it is showing error like this
"Msg 241, Level 16, State 1, Line 10
Conversion failed when converting date and/or time from character string."
 
here MemberShipTB.DOJ is a DateTime column in Table .
so i thing the @startDate and @endDate is been treated as string than DateTime so can anyone suggest for this problem.
 
Thanks in Advance
Posted 19-Feb-13 2:24am
arunrv2.5K
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Insted of declaring your variable as datetime declare it as varchar
as i have declared below and used in your code
 
----------------------
 
declare @QueryExecution varchar(1000),@CrossDBname varchar(500),@StartDate varchar(10),@EndDate varchar(10)
 
  set @StartDate='2013-01-01' 
  set @EndDate='2013-01-31'
 

 
declare @QueryExecution varchar(500),@CrossDBname varchar(40)
   execute @CrossDBname='MarketingDB.dbo.'
 
 
set @QueryExecution= 'SELECT    MemberShipTB.AppNo,  MemberShipTB.MemberID, MemberShipTB.Name, MemberShipTB.ReceiptNo,((MemberShareTb.ShareAmt*MemberShareTb.No_of_Shares)+MemberShareTb.AdminAmt+MemberShareTb.EntranceAmt),MemberShipTB.DOJ       			FROM '+@CrossDBname+' MemberShipTB INNER JOIN '+@CrossDBname+'							 MemberShareTb ON '+@CrossDBname+' MemberShipTB.MemberID = '+@CrossDBname+'MemberShareTb.MemberID where (convert(varchar(10),MemberShipTB.DOJ,121)) between convert(varchar(10),'+@startDate+',121) and convert(varchar(10),'+@endDate+',121)'			
 execute(@QueryExecution)
 

 
I hope it will help you
 
Note: i have not tested this code on my server ,sorry if it does not work.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

just need to append a single quot ''' and convert it to varchar
here is the answer
 
'SELECT    MemberShipTB.AppNo,  MemberShipTB.MemberID, MemberShipTB.Name, MemberShipTB.ReceiptNo,((MemberShareTb.ShareAmt*MemberShareTb.No_of_Shares)+MemberShareTb.AdminAmt+MemberShareTb.EntranceAmt),MemberShipTB.DOJ
                            FROM '+@CrossDBname+' MemberShipTB INNER JOIN '+@CrossDBname+'
                             MemberShareTb ON '+@CrossDBname+' MemberShipTB.MemberID = '+@CrossDBname+'MemberShareTb.MemberID where (MemberShipTB.DOJ between '''+convert(varchar(30),@startDate)+''' and '''+ convert(varchar(30),@endDate)+''')'
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 DamithSL 265
1 CPallini 235
2 OriginalGriff 233
3 Maciej Los 175
4 George Jonsson 170
0 OriginalGriff 5,305
1 DamithSL 4,382
2 Maciej Los 3,760
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,901


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 19 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100