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

avatar 2022年08月21日21:59:15 0 128 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
  • 博客/Java交流群
  • 资源分享,问题解决,技术交流。群号:590480292
  • weinxin
avatar

发表评论

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

  

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