Oracle Live SQL - Script: Using TO_CHAR to Format Dates and Numbers

Statement 1

This statement converts dates as per the format specified in the TO_CHAR function. The iw format represents the week of the year and iyyy format represents a 4-digit year based on the ISO standard.

Converts date value to the specified formats

WITH dates AS (  
  SELECT date'2015-01-01' d FROM dual union  
  SELECT date'2015-01-10' d FROM dual union  
  SELECT date'2015-02-01' d FROM dual 
)  
SELECT d "Original Date", 
       to_char(d, 'dd-mm-yyyy') "Day-Month-Year",  
       to_char(d, 'hh24:mi') "Time in 24-hr format",  
       to_char(d, 'iw-iyyy') "ISO Year and Week of Year" 
FROM dates


Original Date Day-Month-Year Time in 24-hr format ISO Year and Week of Year
01-JAN-15 01-01-2015 00:00 01-2015
10-JAN-15 10-01-2015 00:00 02-2015
01-FEB-15 01-02-2015 00:00 05-2015

3 rows selected.

Original Date	Day-Month-Year	Time in 24-hr format	ISO Year and Week of Year
01-JAN-15	01-01-2015	00:00	01-2015
10-JAN-15	10-01-2015	00:00	02-2015
01-FEB-15	01-02-2015	00:00	05-2015

Statement 2

This statement converts date and timestamp values to the format specified in the TO_CHAR function.

Converts date and timestamp values to specified format

WITH dates AS (  
  SELECT date'2015-01-01' d FROM dual union  
  SELECT date'2015-01-10' d FROM dual union  
  SELECT date'2015-02-01' d FROM dual union 
  SELECT timestamp'2015-03-03 23:44:32' d FROM dual union  
  SELECT timestamp'2015-04-11 12:34:56' d FROM dual   
)  
SELECT d "Original Date", 
       to_char(d, 'dd-mm-yyyy') "Day-Month-Year",  
       to_char(d, 'hh24:mi') "Time in 24-hr format",  
       to_char(d, 'iw-iyyy') "ISO Year and Week of Year", 
       to_char(d, 'Month') "Month Name", 
       to_char(d, 'Year') "Year"  
FROM dates


Original Date Day-Month-Year Time in 24-hr format ISO Year and Week of Year Month Name Year
01-JAN-15 12.00.00.000000 AM 01-01-2015 00:00 01-2015 January  Twenty Fifteen
10-JAN-15 12.00.00.000000 AM 10-01-2015 00:00 02-2015 January  Twenty Fifteen
01-FEB-15 12.00.00.000000 AM 01-02-2015 00:00 05-2015 February Twenty Fifteen
03-MAR-15 11.44.32.000000 PM 03-03-2015 23:44 10-2015 March    Twenty Fifteen
11-APR-15 12.34.56.000000 PM 11-04-2015 12:34 15-2015 April    Twenty Fifteen

5 rows selected.

Original Date	Day-Month-Year	Time in 24-hr format	ISO Year and Week of Year	Month Name	Year
01-JAN-15 12.00.00.000000 AM	01-01-2015	00:00	01-2015	January	Twenty Fifteen
10-JAN-15 12.00.00.000000 AM	10-01-2015	00:00	02-2015	January	Twenty Fifteen
01-FEB-15 12.00.00.000000 AM	01-02-2015	00:00	05-2015	February	Twenty Fifteen
03-MAR-15 11.44.32.000000 PM	03-03-2015	23:44	10-2015	March	Twenty Fifteen
11-APR-15 12.34.56.000000 PM	11-04-2015	12:34	15-2015	April	Twenty Fifteen

Statement 3

This statement extracts the datetime fields specified in the EXTRACT function from the input datetime expressions.

Extracts specified datetime fields from date and timestamp values

WITH dates AS (   
  SELECT date'2015-01-01' d FROM dual union   
  SELECT date'2015-01-10' d FROM dual union   
  SELECT date'2015-02-01' d FROM dual union   
  SELECT timestamp'2015-03-03 23:44:32' d FROM dual union   
  SELECT timestamp'2015-04-11 12:34:56' d FROM dual    
)   
SELECT extract(minute from d) minutes,  
       extract(hour from d) hours,  
       extract(day from d) days,  
       extract(month from d) months,  
       extract(year from d) years  
FROM dates


MINUTES HOURS DAYS MONTHS YEARS
0 0 1 1 2015
0 0 10 1 2015
0 0 1 2 2015
44 23 3 3 2015
34 12 11 4 2015

5 rows selected.

MINUTES	HOURS	DAYS	MONTHS	YEARS
0	0	1	1	2015
0	0	10	1	2015
0	0	1	2	2015
44	23	3	3	2015
34	12	11	4	2015

Statement 4

This statement displays the input numbers as per the format specified in the TO_CHAR function. When no format is specified, as in TO_CHAR(n), then the input number is converted to a VARCHAR2 value exactly long enough to hold its significant digits.

WITH nums AS (  
  SELECT 10 n FROM dual union  
  SELECT 9.99 n FROM dual union  
  SELECT 1000000 n FROM dual  --one million  
)  
SELECT n "Input Number N",  
       to_char(n),  
       to_char(n, '9,999,999.99') "Number with Commas",  
       to_char(n, '0,000,000.000') "Zero-padded Number",  
       to_char(n, '9.9EEEE') "Scientific Notation"  
FROM nums


Input Number N TO_CHAR(N) Number with Commas Zero-padded Number Scientific Notation
9.99 9.99          9.99  0,000,009.990   1.0E+01
10 10         10.00  0,000,010.000   1.0E+01
1000000 1000000  1,000,000.00  1,000,000.000   1.0E+06

3 rows selected.

Input Number N	TO_CHAR(N)	Number with Commas	Zero-padded Number	Scientific Notation
9.99	9.99	9.99	0,000,009.990	1.0E+01
10	10	10.00	0,000,010.000	1.0E+01
1000000	1000000	1,000,000.00	1,000,000.000	1.0E+06

Statement 5

This statement converts the input numbers as per the format specified in the TO_CHAR function. The 'X' format returns the hexadecimal value of the specified number of digits. When the specified number is not an integer, then it is rounded off to the nearest integer.

WITH nums AS (  
  SELECT 10 n FROM dual union  
  SELECT 9.99 n FROM dual union  
  SELECT .99 n FROM dual union  
  SELECT 1000000 n FROM dual  --one million  
)  
SELECT n "Input Number N",  
       to_char(n),  
       to_char(n, '9,999,999.99') "Number with Commas", 
       to_char(n, '0,000,000.000') "Zero_padded Number",  
       to_char(n, '9.9EEEE') "Scientific Notation",  
       to_char(n, '$9,999,990.00') Monetary,  
       to_char(n, 'X') "Hexadecimal Value" 
FROM nums


Input Number N TO_CHAR(N) Number with Commas Zero_padded Number Scientific Notation MONETARY Hexadecimal Value
.99 .99           .99  0,000,000.990   9.9E-01          $0.99  1
9.99 9.99          9.99  0,000,009.990   1.0E+01          $9.99  A
10 10         10.00  0,000,010.000   1.0E+01         $10.00  A
1000000 1000000  1,000,000.00  1,000,000.000   1.0E+06  $1,000,000.00 ##

4 rows selected.

Input Number N	TO_CHAR(N)	Number with Commas	Zero_padded Number	Scientific Notation	MONETARY	Hexadecimal Value
.99	.99	.99	0,000,000.990	9.9E-01	$0.99	1
9.99	9.99	9.99	0,000,009.990	1.0E+01	$9.99	A
10	10	10.00	0,000,010.000	1.0E+01	$10.00	A
1000000	1000000	1,000,000.00	1,000,000.000	1.0E+06	$1,000,000.00	##

Statement 6

This statement converts the input numbers as per the format specified in the TO_CHAR function. The 'X' format returns the hexadecimal value of the specified number of digits. When the specified number is not an integer, then it is rounded off to the nearest integer.

WITH nums AS (  
  SELECT 10 n FROM dual union  
  SELECT 9.99 n FROM dual union  
  SELECT .99 n FROM dual union  
  SELECT 1000000 n FROM dual  --one million  
)  
SELECT n "Input Number N",  
       to_char(n),  
       to_char(n, '9,999,999.99') "Number with Commas",  
       to_char(n, '0,000,000.000') "Zero_padded Number",  
       to_char(n, '9.9EEEE') "Scientific Notation",  
       to_char(n, '$9,999,990.00') Monetary,  
       to_char(n, 'XXXXXX') "Hexadecimal Value"  
FROM nums


Input Number N TO_CHAR(N) Number with Commas Zero_padded Number Scientific Notation MONETARY Hexadecimal Value
.99 .99           .99  0,000,000.990   9.9E-01          $0.99       1
9.99 9.99          9.99  0,000,009.990   1.0E+01          $9.99       A
10 10         10.00  0,000,010.000   1.0E+01         $10.00       A
1000000 1000000  1,000,000.00  1,000,000.000   1.0E+06  $1,000,000.00   F4240

4 rows selected.

Using TO_CHAR to Format Dates and Numbers

Additional Information