Conversion Functions

 

TO_CHAR Function:

Converts dates or numbers into text.

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

Output:

formatted_date
05-MAR-2025 14:30:00

Formats the current date in DD-MON-YYYY HH24:MI:SS format.

 

TO_NUMBER Function:

Converts a string to a number.

SELECT TO_NUMBER('1000') + 500 AS result FROM dual;

Output

result
1500

Converts the string '1000' into a number before adding 500.

TO_DATE Function:

Converts a string into a date format.

SELECT TO_DATE('05-MAR-2025', 'DD-MON-YYYY') AS formatted_date FROM dual;

Output

formatted_date
05-MAR-2025

Converts the string '05-MAR-2025' into an actual date.

ADD_MONTHS - 

User for adding and subtracting months and years 

SELECT 

    SYSDATE AS today,

    ADD_MONTHS(SYSDATE, 6)    AS six_months_ahead,

    ADD_MONTHS(SYSDATE, -6)    AS six_months_ago,

    ADD_MONTHS(SYSDATE, -12*5) AS five_years_ago

    ADD_MONTHS(SYSDATE, 12*5) AS five_years_ahead

FROM dual;