BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle 19c extract CAST Tips

2021-07-22

Oracle 19c extract CAST Tips

extract funcation

Extract function:

oracle中extract()函数从oracle 9i中引入的,主要作用于一个date或者interval类型中截取特定的部分
extract()语法如下:
extract (
{ year | month | day | hour | minute | second | 某一时区 }
from { date类型值 | interval类型值} )
SELECT 
EXTRACT (YEAR FROM DATE '2021-07-22') AS YEAR,
EXTRACT (MONTH FROM DATE '2021-07-22') AS MONTH,
EXTRACT (DAY FROM DATE '2021-07-22') AS DAY
FROM DUAL;

年月日可以根据上面情况直接 EXTRACT 如果是小时,分,秒的话可以结合使用 CAST

SELECT 
EXTRACT( HOUR FROM CAST( datetime AS TIMESTAMP ) ) AS Hours,
EXTRACT( MINUTE FROM CAST( datetime AS TIMESTAMP ) ) AS Minutes,
EXTRACT( SECOND FROM CAST( datetime AS TIMESTAMP ) ) AS Seconds
FROM (
SELECT TO_DATE( '2021-07-22 09:13:10', 'YYYY-MM-DD HH24:MI:SS' ) AS datetime FROM DUAL);

     HOURS    MINUTES	 SECONDS
---------- ---------- ----------
	 9	   13	      10

SELECT 
EXTRACT( YEAR FROM CAST( datetime AS TIMESTAMP ) ) AS YEAR,
EXTRACT( MONTH FROM CAST( datetime AS TIMESTAMP ) ) AS MONTH,
EXTRACT( DAY FROM CAST( datetime AS TIMESTAMP ) ) AS DAY,
EXTRACT( HOUR FROM CAST( datetime AS TIMESTAMP ) ) AS Hours,
EXTRACT( MINUTE FROM CAST( datetime AS TIMESTAMP ) ) AS Minutes,
EXTRACT( SECOND FROM CAST( datetime AS TIMESTAMP ) ) AS Seconds
FROM (
SELECT sysdate AS datetime FROM DUAL);


HONG@pdb1>
SELECT
EXTRACT( YEAR FROM CAST( datetime AS TIMESTAMP ) ) AS YEAR,
EXTRACT( MONTH FROM CAST( datetime AS TIMESTAMP ) ) AS MONTH,
EXTRACT( DAY FROM CAST( datetime AS TIMESTAMP ) ) AS DAY,
EXTRACT( HOUR FROM CAST( datetime AS TIMESTAMP ) ) AS Hours,
EXTRACT( MINUTE FROM CAST( datetime AS TIMESTAMP ) ) AS Minutes,
EXTRACT( SECOND FROM CAST( datetime AS TIMESTAMP ) ) AS Seconds
  8  FROM (
  9  SELECT sysdate AS datetime FROM DUAL);

      YEAR	MONTH	     DAY      HOURS    MINUTES	  SECONDS
---------- ---------- ---------- ---------- ---------- ----------
      2021	    7	      22	 14	    23	       15

HONG@pdb1>

CAST function

CAST()函数可以进行数据类型的转换。

CAST converts values from one data type to another.

CAST()函数的参数有两部分,源值和目标数据类型,中间用AS关键字分隔。

cast_data_type

HONG@pdb1> SELECT CAST('123.4567' AS NUMBER(10,2))  AS NUM FROM DUAL;

       NUM
----------
    123.46

HONG@pdb1>

Reference

SQL Language Reference/CAST

Have a good work&life! 2021/07 via LinHong


Similar Posts

Comments