如何在两个日期之间获取数据 [英] how to get data between two dates
问题描述
我正在使用TMAST_DATE列加入table1中的两个表我选择列日期不应该给出我在下面使用的结果代码请帮助任何一个
< pre lang =SQL> 选择 IR.TMAST_DESCRIPTION,IR.TMAST_QUANTITY_ISSUED,TM.TMAST_QTY 来自 INSERTREPORT < span class =code-keyword> as IR
inner join
TMAST as TM IR.TMAST_BIN_NO = TM.TMAST_BIN_NO
其中 IR.TMAST_DATE = 11/11/2013 和 IR.TMAST_DATE = 2013年12月31日
这有效:
create table test
(
id int 身份,
dt datetime
)
插入 进入 test values (getdate()),(getdate ()),(GETDATE()),(GETDATE()),(GETDATE()),(GETDATE()),(GETDATE()),(GETDATE()),(GETDATE()),(GETDATE() ),(getdate())
选择 * 来自 test 其中 dt ' 2013/12/30' 和 ' 2014/01/01'
你应该永远不要将日期时间转换为字符串来转换它们,这就要求数据库无缘无故地工作。您应始终指定日期为YYYY / MM / DD,因为它在任何SQL Server上都相同,与dd / mm / yyyy或mm / dd / yyyy不同。
所以,修复你的SQL:
选择 IR.TMAST_DESCRIPTION, IR.TMAST_QUANTITY_ISSUED,TM.TMAST_QTY 来自 INSERTREPORT as IR
内部 加入
TMAST TM on IR.TMAST_BIN_NO = TM.TMAST_BIN_NO
其中 IR.TMAST_DATE> = ' 2013/11/11' 和 IR.TMAST_DATE< = ' 2013/12/31'
其中IR.TMAST_DATE在'11 / 11/2013'和'31 / 12/2013之间'
将上一个陈述替换为上述陈述......
你的问题将得到解决..
< blockquote>
选择 IR.TMAST_DESCRIPTION,IR.TMAST_QUANTITY_ISSUED,TM.TMAST_QTY 来自 INSERTREPORT as IR
inner join
TMAST as TM IR.TMAST_BIN_NO = TM.TMAST_BIN_NO
< span class =code-keyword>其中 转换( varchar ( 10 ),IR.TMAST_DATE, 101 ) ' 11/11/2013' 和 ' 31 /二千零十三分之一十二' 跨度>
i am joining two tables in table1 using TMAST_DATE column i am selecting that column date should not give the result i am using below code please help any one
select IR.TMAST_DESCRIPTION,IR.TMAST_QUANTITY_ISSUED,TM.TMAST_QTY from INSERTREPORT as IR
inner join
TMAST as TM on IR.TMAST_BIN_NO=TM.TMAST_BIN_NO
where IR.TMAST_DATE=11/11/2013 and IR.TMAST_DATE=31/12/2013
This works:
create table test ( id int identity, dt datetime ) insert into test values (getdate()),(getdate()),(getdate()),(getdate()),(getdate()),(getdate()),(getdate()),(getdate()),(getdate()),(getdate()),(getdate()) select * from test where dt between '2013/12/30' and '2014/01/01'
You should NEVER convert date times to strings to convert them, that's asking the DB to work for no reason. You should always specify dates a YYYY/MM/DD because that works the same on any SQL Server, unlike dd/mm/yyyy or mm/dd/yyyy.
So, to fix your SQL:
select IR.TMAST_DESCRIPTION,IR.TMAST_QUANTITY_ISSUED,TM.TMAST_QTY from INSERTREPORT as IR inner join TMAST as TM on IR.TMAST_BIN_NO=TM.TMAST_BIN_NO where IR.TMAST_DATE >= '2013/11/11' and IR.TMAST_DATE <='2013/12/31'
where IR.TMAST_DATE between '11/11/2013' and '31/12/2013'
Replace your last statement with the above statement...
Your problem will be solved..
select IR.TMAST_DESCRIPTION,IR.TMAST_QUANTITY_ISSUED,TM.TMAST_QTY from INSERTREPORT as IR inner join TMAST as TM on IR.TMAST_BIN_NO=TM.TMAST_BIN_NO where Convert(varchar(10),IR.TMAST_DATE,101) between '11/11/2013' and '31/12/2013'
这篇关于如何在两个日期之间获取数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!