oracle获取当年所有的周、月、季度

初夏*参殇

于 2024-07-19 14:51:10 发布

阅读量370

点赞数 1

分类专栏： # oracle数据库系列数据库文章标签： oracle 数据库

本文链接：https://blog.csdn.net/weixin_44989103/article/details/140549124

版权

数据库同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

oracle数据库系列

2 篇文章 0 订阅

订阅专栏

oracle获取当年所有的周、月、季度

1、oracle获取当年所有的周

SELECT
	TRUNC( trunc( SYSDATE, 'yyyy' ) + ( ROWNUM - 1 ) * 7, 'D' ) + 1 AS KSSJ,
	trunc( ( TRUNC( trunc( SYSDATE, 'yyyy' ) + ( ROWNUM - 1 ) * 7, 'D' ) + 7 ) + 1 ) - 1 / ( 24 * 3600 ) AS JSSJ,
	TO_CHAR( trunc( SYSDATE, 'yyyy' ) + ( ROWNUM - 1 ) * 7, 'iw' ) AS XH,
	TO_CHAR( TRUNC( trunc( SYSDATE, 'yyyy' ) + ( ROWNUM - 1 ) * 7, 'D' ) + 1, 'YYYY' ) AS NF,
	(
		'W' || TO_CHAR( TRUNC( trunc( SYSDATE, 'yyyy' ) + ( ROWNUM - 1 ) * 7, 'D' ) + 1, 'YYYY' ) || '-' || TO_CHAR( trunc( SYSDATE, 'yyyy' ) + ( ROWNUM - 1 ) * 7, 'iw' ) 
	) AS BSM,
	(
		TO_CHAR( TRUNC( trunc( SYSDATE, 'yyyy' ) + ( ROWNUM - 1 ) * 7, 'D' ) + 1, 'YYYY' ) || '年第' || TO_NUMBER( TO_CHAR( trunc( SYSDATE, 'yyyy' ) + ( ROWNUM - 1 ) * 7, 'iw' ) ) || '周' 
	) AS QJMS 
FROM
	DUAL CONNECT BY ROWNUM <= TRUNC( add_months( trunc( SYSDATE, 'yyyy' ), 12 ) - trunc( SYSDATE, 'yyyy' ) ) / 7 + 1

2、oracle获取当年所有的月

SELECT
	trunc(to_DATE(TO_CHAR(ADD_MONTHS(trunc(sysdate, 'yyyy'), ROWNUM - 1), 'yyyyMM'), 'YYYYMM'), 'mm') AS KSSJ,
	trunc(last_day(to_date(TO_CHAR(ADD_MONTHS(trunc(sysdate, 'yyyy'), ROWNUM - 1), 'yyyyMM'),'yyyy-mm')+ (ROWNUM - 1)) +1)-1/(24*3600)  AS JSSJ,
	TO_CHAR(ADD_MONTHS(trunc(sysdate, 'yyyy'), ROWNUM - 1), 'MM') AS XH,
	TO_CHAR(ADD_MONTHS(trunc(sysdate, 'yyyy'), ROWNUM - 1), 'yyyy') AS NF,
	(
		'M' || TO_CHAR(ADD_MONTHS(trunc(sysdate, 'yyyy'), ROWNUM - 1), 'yyyy')  || '-' || TO_CHAR(ADD_MONTHS(trunc(sysdate, 'yyyy'), ROWNUM - 1), 'MM')
	) AS BSM,
	(
		TO_CHAR(ADD_MONTHS(trunc(sysdate, 'yyyy'), ROWNUM - 1), 'yyyy')  || '年' || TO_NUMBER(TO_CHAR(ADD_MONTHS(trunc(sysdate, 'yyyy'), ROWNUM - 1), 'MM') ) || '月' 
	) AS QJMS 
FROM DUAL
CONNECT BY ROWNUM <=months_between(add_months(trunc(sysdate, 'yyyy'), 12), trunc(sysdate, 'yyyy'))

3、获取当年年份

SELECT
	trunc(sysdate, 'yyyy') AS KSSJ,
	add_months(trunc(sysdate, 'yyyy'), 12) - 1/(24*3600)  AS JSSJ,
	TO_CHAR(sysdate, 'yyyy') AS XH,
	TO_CHAR(sysdate, 'yyyy') AS NF,
	(
		'Y' || TO_CHAR(sysdate, 'yyyy')
	) AS BSM,
	(TO_CHAR(sysdate, 'yyyy') || '年' ) AS QJMS 
FROM DUAL

3、oracle获取当年所有的季度

SELECT
	trunc(to_DATE(TO_CHAR(ADD_MONTHS(trunc(sysdate, 'yyyy'), (ROWNUM - 1)*3), 'yyyyMM'), 'YYYYMM'), 'mm') AS KSSJ,
	trunc(to_DATE(TO_CHAR(ADD_MONTHS(trunc(sysdate, 'yyyy'), (ROWNUM - 1)*3+3), 'yyyyMM'), 'YYYYMM'), 'mm')-1/(24*3600)  AS JSSJ,
	TO_CHAR(ADD_MONTHS(TO_DATE('2022-01', 'yyyy-MM'), (ROWNUM - 1)*3), 'q')  AS XH,
	TO_CHAR(ADD_MONTHS(TO_DATE('2022-01', 'yyyy-MM'), (ROWNUM - 1)*3), 'yyyy') AS NF,
	(
		'Q' || TO_CHAR(ADD_MONTHS(TO_DATE('2022-01', 'yyyy-MM'), (ROWNUM - 1)*3), 'yyyy')   || '-' || TO_CHAR(ADD_MONTHS(sysdate, (ROWNUM - 1)*3), 'q')
	) AS QJBSM,
	(
		TO_CHAR(ADD_MONTHS(TO_DATE('2022-01', 'yyyy-MM'), (ROWNUM - 1)*3), 'yyyy') || '年第' || 
		TO_CHAR(ADD_MONTHS(TO_DATE('2022-01', 'yyyy-MM'), (ROWNUM - 1)*3), 'q')  || '季度' 
	) AS QJMS 
FROM DUAL
CONNECT BY ROWNUM <=months_between(add_months(trunc(sysdate, 'yyyy'), 12), trunc(sysdate, 'yyyy'))/3