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

avatar 2022年08月21日21:59:15 0 1186 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
  • 微信
  • 交流学习,资料分享
  • weinxin
  • 个人淘宝
  • 店铺名:言曌博客咨询部

  • (部分商品未及时上架淘宝)
avatar

发表评论

avatar 登录者:匿名
匿名评论,评论回复后会有邮件通知

  

已通过评论:0   待审核评论数:0