Using Conversion Functions

Using the SQL Character Conversion Function

-- you can convert the system date (SYSDATE) to a character string and format
-- with various format models and then display the date as follows
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY AD') "Today" FROM DUAL;
-- FM removes all leading or trailing blanks from Month
SELECT TO_CHAR(SYSDATE, 'FMMonth DD YYYY') "Today" FROM DUAL;
-- the following displays the system date and time with a format model
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Now" FROM DUAL;

-- you can convert and format a date column using format models 
-- for Short or Long Date format
SELECT hire_date, TO_CHAR(hire_date,'DS') "Short Date" FROM employees;
SELECT hire_date, TO_CHAR(hire_date,'DL') "Long Date" FROM employees;

-- the following extracts the year, month, and date from SYSDATE, then converts 
-- and formats the result with leading zeros and removes any leading blanks (FM)
SELECT TO_CHAR(EXTRACT(YEAR FROM SYSDATE)) || 
  TO_CHAR(EXTRACT(MONTH FROM SYSDATE),'FM09') || 
  TO_CHAR(EXTRACT(DAY FROM SYSDATE),'FM09') "Current Date" FROM DUAL;

-- the following returns the current date in the session time zone, 
-- in a value in the Gregorian calendar of datatype DATE, 
-- the returned value is converted to character and displayed with a format model
SELECT TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH24:MI:SS') "Current Date" FROM DUAL;

-- you can convert and format numeric currency data as a character string 
-- with a format model to add a $, commas, and deciaml point
SELECT TO_CHAR(salary,'$99,999.99') salary FROM employees;

Using the SQL Number Conversion Function

-- you can convert a character string to a number
SELECT TO_NUMBER('1234.99') + 500 FROM DUAL;

-- the format model must match the format of the string you want to convert
SELECT TO_NUMBER('11,200.34', '99G999D99') + 1000 FROM DUAL;

Using SQL Date Conversion Functions

-- the following converts the character string to a date with 
-- the specified format model
SELECT TO_DATE('27-OCT-98', 'DD-MON-RR') FROM DUAL;

-- the following converts the character string to a date with 
-- the specified format model
SELECT TO_DATE('28-Nov-05 14:10:10', 'DD-Mon-YY HH24:MI:SS') FROM DUAL;

-- the following converts the character string to a date with 
-- the specified format model
SELECT TO_DATE('January 15, 2006, 12:00 A.M.', 'Month dd, YYYY, HH:MI A.M.')
  FROM DUAL;

-- the following converts a character stirng to a timestamp with 
-- the specified datetime format model
SELECT TO_TIMESTAMP('10-Sep-05 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
  FROM DUAL;