EXTRACT and Date Parts
ExplanationOracle Fusion / EBS · Technical · PL/SQLHigh confidenceGenerated
EXTRACT(field FROM value) returns one component (YEAR, MONTH, DAY, HOUR, ...) as a number.
How it works
EXTRACT(YEAR FROM d), EXTRACT(MONTH FROM d) and EXTRACT(DAY FROM d) work on DATE and TIMESTAMP. HOUR, MINUTE, SECOND and TIMEZONE_HOUR require a TIMESTAMP (cast a DATE first). It is cleaner than TO_CHAR for arithmetic because it returns a NUMBER, not text. TO_CHAR(d, 'IW') / 'Q' / 'DAY' cover ISO week, quarter and weekday name where EXTRACT has no field.
How it works
SELECT EXTRACT(YEAR FROM SYSDATE) AS yr,
EXTRACT(MONTH FROM SYSDATE) AS mth,
EXTRACT(DAY FROM SYSDATE) AS dy,
EXTRACT(HOUR FROM SYSTIMESTAMP) AS hr,
TO_CHAR(SYSDATE, 'Q') AS quarter,
TO_CHAR(SYSDATE, 'IW') AS iso_week
FROM dual;Related questions
GroundingGenerated
Model-generated, grounded against the curated knowledge layer. Check specifics against your instance.