使用where条件从交叉数据库中获取数据时的SQL查询问题 [英] SQL Query problem in fetching data from cross DataBase using where condition

问题描述

大家好，

我在从其他数据库中获取数据时遇到问题。

我有2个数据库



1.BankingDB

2.MarketingDB


这2 DB中的
我有一个相同的表MembersTB

即在银行业我有表MembersTB，我在MarketingDB中也有表MembersTB也有相同的列名。



现在我需要显示从两个DataBase中的StartDate到EndDate的成员记录。我需要附加两个表，所以我在银行表中写入查询。对于银行业务表选择查询不是问题，但我在从另一个数据库（MarketingDB）获取数据时遇到问题。



银行查询（好）

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)

这里我指定数据库名称以便从其他数据库中获取数据。



但它显示的错误是这样的

Msg 241，Level 16，State 1，Line 10

转换失败时从字符串转换日期和/或时间。



这里的MemberShipTB.DOJ是表格中的DateTime列。

所以我的事情是@startDate和@endDate被视为字符串而不是DateTime所以任何人都可以建议这个问题。



先谢谢

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

推荐答案

将你的变量声明为datetime，并将其声明为varchar

，因为我已在下面声明并在你的代码中使用



----------------------

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.

只需附加一个单词' ''''并将其转换为varchar

这里是答案

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)+''')'

这篇关于使用where条件从交叉数据库中获取数据时的SQL查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！