sql查询构建时间组件，今天，近三天，本周，本月，上个月，本年等

sqlserver
-- 今天
SELECT '1' type,CONVERT(varchar(100), GETDATE(), 23) value1 ,CONVERT(varchar(100), GETDATE(), 23) value2
-- 本周
union all SELECT '2' type,CONVERT(varchar(100),dateadd(wk,datediff(wk,0,getdate()),0) , 23)  value1, CONVERT(varchar(100), dateadd(wk,datediff(wk,0,getdate()),6), 23)  value2 
-- 本月
union all SELECT '3' type,CONVERT(varchar(100), dateadd(dd,-day(getdate())+1,getdate()) , 23) value1, CONVERT(varchar(100), dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) , 23) value2 
-- 本季
union all SELECT '4' type,CONVERT(varchar(100), DATEADD(qq,   DATEDIFF(qq,0,getdate()),   0), 23) value1,CONVERT(varchar(100), dateadd(qq , datediff(QUARTER , -1, getdate()), -1), 23) value2 
-- 本年
union all SELECT '5' type,CONVERT(varchar(100),  dateadd(year, datediff(year, 0, getdate()), 0), 23) value1, CONVERT(varchar(100), dateadd(year, datediff(year, -1, getdate()), -1), 23) value2 
-- 上个月
union all SELECT '7' type,CONVERT(varchar(100), DATEADD(mm, DATEDIFF(m, 0, GETDATE()) - 1, 0) , 23) value1,  CONVERT(varchar(100), DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)) , 23) value2  
-- 上一年
union all SELECT '8' type, CONVERT(varchar(100),DATEADD(yy, DATEDIFF(yy,0,getdate())-1,   0), 23)  value1,  CONVERT(varchar(100),dateadd(ms,-3,DATEADD(yy,   DATEDIFF(yy,0,getdate()),   0)), 23)  value2  
-- 近三天
union all SELECT '9' type,CONVERT(varchar(100), dateadd(day,-2,GETDATE()), 23) value1, CONVERT(varchar(100),dateadd(day,2,GETDATE()), 23) value2
-- 近五天
union all SELECT '10' type,CONVERT(varchar(100), dateadd(day,-4,GETDATE()), 23) value1, CONVERT(varchar(100),dateadd(day,4,GETDATE()), 23) value2
-- 近七天
union all SELECT '11' type,CONVERT(varchar(100), dateadd(day,-6,GETDATE()), 23) value1, CONVERT(varchar(100),dateadd(day,6,GETDATE()), 23) value2








oracle


SELECT '1' type,to_char(sysdate, 'YYYY-MM-DD') value1,to_char(sysdate, 'YYYY-MM-DD') value2 FROM dual 
union all SELECT '2' type,to_char(trunc(sysdate, 'd') + 1,'YYYY-MM-DD') value1,to_char(trunc(sysdate, 'd') + 7,'YYYY-MM-DD') value2 FROM dual 
union all SELECT '3' type,to_char(trunc(sysdate, 'mm'),'YYYY-MM-DD') value1,to_char(last_day(trunc(sysdate)),'YYYY-MM-DD') value2 FROM dual 
union all SELECT '4' type,to_char(TRUNC(SYSDATE, 'Q'), 'YYYY-MM-DD') value1,to_char(add_months(trunc(sysdate, 'Q'), 3) - 1,'YYYY-MM-DD') value2 FROM dual 
union all SELECT '5' type,to_char(trunc(sysdate, 'yyyy'),'YYYY-MM-DD') value1,to_char(add_months(trunc(sysdate, 'yyyy'), 12) - 1,'YYYY-MM-DD') value2 FROM dual 
union all SELECT '7' type,to_char(to_date(to_char(add_months(sysdate, -1), 'yyyy-mm'),'yyyy-mm'),'yyyy-mm-dd') value1,to_char(last_day(add_months(sysdate,-1)),'yyyy-mm-dd') value2 FROM dual 
union all SELECT '8' type,to_char(trunc(ADD_MONTHS(sysdate,-12), 'yyyy'),'YYYY-MM-DD') value1,to_char(trunc(sysdate, 'yyyy')-1,'YYYY-MM-DD') value2 FROM dual 
union all SELECT '9' type,to_char(sysdate - 2,'YYYY-MM-DD') value1,to_char(sysdate + 2,'YYYY-MM-DD') value2 FROM dual 
union all SELECT '10' type,to_char(sysdate - 4,'YYYY-MM-DD') value1,to_char(sysdate + 4,'YYYY-MM-DD') value2 FROM dual 
union all SELECT '11' type,to_char(sysdate - 6,'YYYY-MM-DD') value1,to_char(sysdate + 6,'YYYY-MM-DD') value2 FROM dual