oracle sql获取上个月数据:
// 如果日期字段是varchar类型 写法1
SELECT *
FROM student
WHERE to_char(to_date(create_date, 'YYYYMMdd'), 'YYYYMM') =
to_char(add_months(SYSDATE, -1), 'YYYYMM')
// 如果日期字段是varchar类型 写法2
SELECT *
FROM student a
WHERE substr(a.create_date, 0, 6) = to_char(SYSDATE - INTERVAL '1' MONTH - 1, 'yyyyMM')
// 如果日期字段是date类型 写法1
SELECT *
FROM student
WHERE to_char(create_time, 'YYYYMM') = to_char(add_months(SYSDATE, -1), 'YYYYMM')
// 如果日期字段是date类型 写法2
SELECT *
FROM student a
WHERE to_char(a.create_date, 'yyyyMM') =
to_char(SYSDATE - INTERVAL '1' MONTH - 1,'yyyyMM')
获取这个月的数据
// 如果日期字段是varchar类型
SELECT *
FROM student a
WHERE substr(a.create_time, 0, 6) = to_char(SYSDATE , 'yyyyMM')
// 如果日期字段是date类型
SELECT *
FROM student a
WHERE to_char(a.create_time, 'yyyyMM') = to_char(SYSDATE , 'yyyyMM')
获取上一年年份
// 如果日期字段是varchar类型
SELECT *
FROM student a
WHERE substr(a.create_time, 0, 4) = (to_char(sysdate,'yyyy')-1)
// 如果日期字段是date类型
SELECT *
FROM student a
WHERE to_char(a.create_time, 'yyyy') = (to_char(sysdate,'yyyy')-1)