我现在是分开3个查询 where year = substr('2013-01-01',1,4)
and month >= substr('2013-01-01',6,8)unionwhere year > substr('2013-01-01',1,4)
and year < substr('2014-05-05',6,8)unionwhere year = substr('2014-05-05',1,4)
and month <= substr('2014-05-05',6,8)这样写的话同年就有问题了 大神们懂我意思吗 求解决方案
and month >= substr('2013-01-01',6,8)unionwhere year > substr('2013-01-01',1,4)
and year < substr('2014-05-05',6,8)unionwhere year = substr('2014-05-05',1,4)
and month <= substr('2014-05-05',6,8)这样写的话同年就有问题了 大神们懂我意思吗 求解决方案
SELECT id FROM (WITH TEST AS(
SELECT 1 ID,'2013' YEAR,'01' MONTH FROM DUAL UNION ALL
SELECT 2 ,'2014' ,'05' FROM DUAL UNION ALL
SELECT 3 ,'2014' ,'06' FROM DUAL
)SELECT ID,TO_DATE(YEAR||MONTH||'01','YYYYMMDD') D FROM TEST) T
WHERE T.D BETWEEN TO_DATE('2013-01-01','YYYY-MM-DD') AND TO_DATE('2014-05-05','YYYY-MM-DD')结果只要id是吗?
where ym between to_date('2013-01-01' ,'yyyy-mm-dd') and to_date( '2014-05-05' ,'yyyy-mm-dd');
WITH TEST AS(
SELECT 1 ID,'2013' YEAR,'01' MONTH FROM DUAL UNION ALL
SELECT 2 ,'2014' ,'05' FROM DUAL UNION ALL
SELECT 3 ,'2014' ,'06' FROM DUAL)
select id from(select id,to_date(year||month,'yyyy-mm') as t_time from test)
where
t_time between to_date(substr('2013-01-01',0,7),'yyyy-mm') and to_date(substr('2014-05-05',0,7),'yyyy-mm');
-- 2. 语句未测试,不保证一下子就跑通。
select id from mytable
where to_date(year || month || '01','yyyymmdd') between to_date('2013-01-01' ,'yyyy-mm-dd') and to_date( '2014-05-05' ,'yyyy-mm-dd')
--
select id from mytable
where year || month between '201301' and '201405‘
then, the query statement should be like this:
select id
from P
where to_date(year||month,'YYYYMM') between to_date('2013-01-01' ,'YYYY-MM-DD') and to_date('2014-05-05' ,'YYYY-MM-DD')现在有张表是这样的 分别存着年度与月份 然后现在我有两个日期 一个开始日期 一个结束日期 求查在日期间内的数据表:
id year month
1 2013 01
2 2014 05
3 2014 06 开始日期 '2013-01-01' 结束日期 '2014-05-05'