Oracle和MySQL查询当月已过去的日期

言曌 2022年08月21日21:59:15 0 1149 views

博主分享免费Java教学视频，B站账号：Java刘哥，长期提供技术问题解决、项目定制：本站商品点此

最近写了很多SQL，记录下

查询本月已经过去的日期：如 2022-08-01、2022-08-02、...、2022-08-21

Oracle写法

select to_char(TRUNC(sysdate, 'MM') + ROWNUM - 1, 'yyyy-mm-dd') as TIME from DUAL connect by ROWNUM <= TO_NUMBER(
TO_CHAR(sysdate, 'dd'))

MySQL写法

SELECT ADDDATE(tab2.firstDay, tab1.firstDay - 1) AS TIME FROM ( SELECT 1 AS firstDay UNION ALL SELECT 2 UNION ALL SELECT
3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27
UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31 ) tab1, ( SELECT CURDATE() - INTERVAL
DAY(CURDATE()) - 1 DAY AS firstDay, DAY(CURDATE()) AS today ) tab2 WHERE tab1.firstDay <= tab2.today