Commonly used Oracle date functions with examples

 SYSDATE: Returns the current system date and time.

SELECT SYSDATE FROM DUAL;

TO_DATE: Converts a string to a date value.

SELECT TO_DATE('2024-05-06', 'YYYY-MM-DD') FROM DUAL;

The TO_DATE function in Oracle is used to convert a character string to a date value. When using TO_DATE, you need to specify the format of the input string so that Oracle can correctly interpret it. Here are some commonly used format elements for specifying date formats in Oracle:

1. YYYY: Four-digit year.
2. MM: Two-digit month (01-12).
3. DD: Two-digit day of the month (01-31).
4. HH: Two-digit hour (00-23).
5. MI: Two-digit minute (00-59).
6. SS: Two-digit second (00-59).
7. AM or PM: Meridian indicator.
8. MON: Abbreviated month name (e.g., JAN, FEB, MAR).
9. MONTH: Full month name (e.g., JANUARY, FEBRUARY, MARCH).
10. DY: Abbreviated day name (e.g., SUN, MON, TUE).
11. DAY: Full day name (e.g., SUNDAY, MONDAY, TUESDAY).
12. TZH: Time zone hour.
13. TZM: Time zone minute.

TO_CHAR: Converts a date value to a string in a specified format.

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') FROM DUAL;

ADD_MONTHS: Adds a specified number of months to a date.

SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;

MONTHS_BETWEEN: Calculates the number of months between two dates.

SELECT MONTHS_BETWEEN('2024-05-06', '2023-01-15') FROM DUAL;

LAST_DAY: Returns the last day of the month for a given date.

SELECT LAST_DAY(SYSDATE) FROM DUAL;

TRUNC: Truncates a date value to a specified precision (day, month, year, etc.).

SELECT TRUNC(SYSDATE, 'MM') FROM DUAL; -- Truncate to the beginning of the current month

ROUND: Rounds a date value to a specified precision (day, month, year, etc.).

SELECT ROUND(SYSDATE, 'MONTH') FROM DUAL; -- Round to the beginning of the current month

NEXT_DAY: Finds the next specified day of the week after a given date.

SELECT NEXT_DAY(SYSDATE, 'SATURDAY') FROM DUAL;

EXTRACT: Extracts a specific component (year, month, day, hour, minute, etc.) from a date value.

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;

CURRENT_TIMESTAMP: Returns the current date and time including fractional seconds.

SELECT CURRENT_TIMESTAMP FROM DUAL;