Oracle 查询连接日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17878222/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle query concatenate date
提问by Ali Issa
I have the following query:
我有以下查询：
select *
from mytable
where to_char(mydate,'mm/dd/yyyy') between ('05/23/2013')
and ('06/22/2013')
I need to change it to make dynamically so that I wont modify it every month from 05/23/2013to 06/23/2013for example:
我需要改变它，使动态，这样我不会每个月都修改它05/23/2013到06/23/2013，例如：
('05/23/' + (select to_char(sysdate, 'yyyy') from dual))
but this is giving an error, any suggestions?
但这是一个错误，有什么建议吗？
what I need to do: every month i need to run this query to get the records between 23 of this month and 23 of the last month
我需要做的是：每个月我都需要运行此查询以获取本月 23 日和上月 23 日之间的记录
回答by xlecoustillier
Oracle uses ||as the concatenation operator:
Oracle||用作连接运算符：
('05/23/' || (select to_char(sysdate, 'yyyy') from dual))
BTW, David is right. If you really want to compare string representations of dates (but why?), use a date format that is ordered the same way as dates:
顺便说一句，大卫是对的。如果您真的想比较日期的字符串表示（但为什么？），请使用与日期相同的日期格式：
to_char(mydate,'yyyy/mm/dd')
回答by David Aldridge
You're performing a comparison on strings, not on dates, so your code doesn't work the way you think it does.
您正在对字符串而不是日期进行比较，因此您的代码无法按照您认为的方式工作。
Based on the string logic, "05/23/2000" is between "05/22/2013" and "06/24/2000".
根据字符串逻辑，“05/23/2000”介于“05/22/2013”和“06/24/2000”之间。
Keep the data types as date and Oracle will get the comparison right.
将数据类型保留为日期，Oracle 将正确进行比较。
Possibly what you want is:
可能你想要的是：
select *
from my_table
where mydate >= add_months(trunc(sysdate,'MM'),-1)+22 and
mydate < trunc(sysdate,'MM')+22
but it's difficult to tell without a description of what the requirement actually is.
但如果不描述需求的实际情况，就很难说清楚。
回答by Martin
How about this one?
这个怎么样？
SELECT '(''05/23/'''||to_char(sysdate, 'yyyy')||'''' FROM DUAL
Have not testet because I have no Oracle database right now, needs checking for quote escapes...
没有 testet 因为我现在没有 Oracle 数据库，需要检查报价转义...
Do you need exact days from the month? You can also substract days from sysdate:
你需要一个月的确切日期吗？您还可以从 sysdate 中减去天数：
SELECT (sysdate - 30) FROM DUAL
回答by Vinit Prajapati
You may also use concat function
您也可以使用 concat 函数
concat('05/23/', (select to_char(sysdate, 'yyyy') from dual))
