select b.empno,b.buildid
from
(select *
from openquery (TEST, 'select /*+ RULE */ * from db1.ta_show
where compcd=''11'' and corpcd=''C11'' and workdt like ''201301%''
') ) a,(select *
from openquery (TEST, 'select /*+ RULE */ * from db1.tb_show
where compcd=''11'' and corpcd=''C11'' and statuscd=''D''
')) b
where a.id=b.id
这样写可以查出数据,,,如果 workdt like ''201301%''
不要写死的,要是当月,left(CONVERT(varchar(100), GETDATE(), 112),6) 能得到201301但如果把这放进去
select b.empno,b.buildid
from
(select *
from openquery (TEST, 'select /*+ RULE */ * from db1.ta_show
where compcd=''11'' and corpcd=''C11'' and workdt like ''left(CONVERT(varchar(100), GETDATE(), 112),6)%''
') ) a,(select *
from openquery (TEST, 'select /*+ RULE */ * from db1.tb_show
where compcd=''11'' and corpcd=''C11'' and statuscd=''D''
')) b
where a.id=b.id
又不对,得怎么写好呢?
from
(select *
from openquery (TEST, 'select /*+ RULE */ * from db1.ta_show
where compcd=''11'' and corpcd=''C11'' and workdt like ''201301%''
') ) a,(select *
from openquery (TEST, 'select /*+ RULE */ * from db1.tb_show
where compcd=''11'' and corpcd=''C11'' and statuscd=''D''
')) b
where a.id=b.id
这样写可以查出数据,,,如果 workdt like ''201301%''
不要写死的,要是当月,left(CONVERT(varchar(100), GETDATE(), 112),6) 能得到201301但如果把这放进去
select b.empno,b.buildid
from
(select *
from openquery (TEST, 'select /*+ RULE */ * from db1.ta_show
where compcd=''11'' and corpcd=''C11'' and workdt like ''left(CONVERT(varchar(100), GETDATE(), 112),6)%''
') ) a,(select *
from openquery (TEST, 'select /*+ RULE */ * from db1.tb_show
where compcd=''11'' and corpcd=''C11'' and statuscd=''D''
')) b
where a.id=b.id
又不对,得怎么写好呢?
解决方案 »
- 求一自动压缩并传输数据库备份文件至局域网中其他机器中的工具!!
- SQL Profiler事件跟踪里的筛选器怎样监测textdata?
- 问一个关于安装sql server2000 sp4驱动程序安装不上的问题,急,在线等,马上给分!!!
- 用select 能不能按一条记录中的 数量 字段的值列出数量值的行数?
- 请教in的用法?
- 大家好!请教一个SQL问题!在线等,谢谢!
- 爲什麽總是出錯?
- 请问Image类型的数据如何读取出来?
- 分布式分區視圖問題--------困擾俺多時了﹗﹗﹗﹗﹗
- 求教如何使用PowerDesigner设计数据库结构
- sql server 2008无法安装成功
- 这段代码执行结果很郁闷
DECLARE @i DATETIME
SET @i = '2013-01-01'SELECT CASE WHEN datediff(yy,@i,getdate()) =0 and datediff(mm,@i,getdate())=0 THEN '同月' ELSE '不同月' END--同月
字符串的话 convert转称datetime ,如果直接就是datetime,一样用。
DECLARE @i DATETIME
SET @i = '20130101'SELECT CASE WHEN datediff(yy,@i,getdate()) =0 and datediff(mm,@i,getdate())=0 THEN '同月' ELSE '不同月' END--同月
from openquery (TEST, 'select /*+ RULE */ * from db1.ta_show
where compcd=''11'' and corpcd=''C11'' and and datediff(yy,convert(datetime,workdt),getdate()) =0 anddatediff(mm,convert(datetime,workdt),getdate())=0
') ) a,(select *
from openquery (TEST, 'select /*+ RULE */ * from db1.tb_show
where compcd=''11'' and corpcd=''C11'' and statuscd=''D''
')) b
where a.id=b.id
这样也不行啊
from openquery (TEST, 'select /*+ RULE */ * from db1.ta_show
where compcd=''11'' and corpcd=''C11'' and datediff(yy,convert(datetime,workdt),getdate()) =0 and datediff(mm,convert(datetime,workdt),getdate())=0
') ) a,(select *
from openquery (TEST, 'select /*+ RULE */ * from db1.tb_show
where compcd=''11'' and corpcd=''C11'' and statuscd=''D''
')) b
where a.id=b.id
这样也不行啊
(select *
from openquery (TEST, 'select /*+ RULE */ * from db1.ta_show
where compcd=''11'' and corpcd=''C11'' and substr(workdt,1,6) =to_char(sysdate,''yyyymm'')
') ) a,(select *
from openquery (TEST, 'select /*+ RULE */ * from db1.tb_show
where compcd=''11'' and corpcd=''C11'' and statuscd=''D''
')) b
where a.id=b.id