Converting Datetimes Using TO_CHAR() and TO DATE()

Oracle has functions that enable you to convert a value in one data type to another. You saw some of these functions in the previous chapter. In this section, you ll see how to use TO_CHAR() and TO_DATE() to convert between strings and datetimes. Table 4-1 summarizes the TO_CHAR() and TO_DATE() functions.

Table 4-1: TO_CHAR() and TO_DATE() Conversion Functions

Function	Description
TO_CHAR( x [, format ])	Converts the number or datetime x to a string. You can also supply an optional format for x . You saw how to use TO_CHAR() to convert a number to a string in the previous chapter.
TO_DATE( x [, format ])	Converts the string x to a DATE .

Let s start off by examining how you use TO_CHAR() to convert a datetime to a string. Later, you ll see how to use TO_DATE() to convert a string to a DATE.

Using TO_CHAR() to Convert a Datetime to a String

You can use TO_CHAR(x[, format]) to convert the datetime x to a string. You can also provide an optional format that indicates the format of x. An example format is MONTH DD, YYYY , where:

• MONTH is the full name of the month in uppercase such as JANUARY

• DD is the two-digit day

• YYYY is the four-digit year

The following example uses TO_CHAR() to convert the dob column from the customers table to a string with the format MONTH DD, YYYY :

  SELECT customer_id, TO_CHAR(dob, 'MONTH DD, YYYY')   FROM customers;  CUSTOMER_ID TO_CHAR(DOB,'MONTH ----------- ------------------           1 JANUARY   01, 1965           2 FEBRUARY  05, 1968           3 MARCH     16, 1971           4           5 MAY       20, 1970 

The next example gets the current date and time from the database using the SYSDATE function and converts it to a string using TO_CHAR() with the format MONTH DD, YYYY, HH24:MI:SS. The time portion of this format indicates that the hours are in 24- hour format, along with the minutes and seconds.

  SELECT TO_CHAR(SYSDATE, 'MONTH DD, YYYY, HH24:MI:SS')   FROM dual;  TO_CHAR(SYSDATE,'MONTHDD,YYY ---------------------------- OCTOBER   21, 2003, 19:32:36 

When you use TO_CHAR() to convert a datetime to a string, the format string has a number of parameters that affect the returned string. Some of these parameters are listed in Table 4-2.

Table 4-2: Datetime Formatting Parameters

Aspect	Parameter	Description	Example
Century	CC	Two-digit century.	21
	SCC	Two-digit century with a negative sign ( “) for B.C.	“10
Quarter	Q	One-digit quarter of the year.	1
Year	YYYY	All four digits of the year.	2006
	IYYY	All four digits of the ISO year.	2006
	RRRR	All four digits of the rounded year, which depends on the current year. See the section How Oracle Interprets Two-Digit Years for details.	2006
	SYYYY	All four digits of the year with a negative sign ( “) for B.C.	“1001
	Y,YYY	All four digits of the year with a comma.	2,006
	YYY	Last three digits of the year.	006
	IYY	Last three digits of the ISO year.	006
	YY	Last two digits of the year.	06
	IY	Last two digits of the ISO year.	06
	RR	Last two digits of the rounded year, which depends on the current year. See the section How Oracle Interprets Two-Digit Years for details.	06
	Y	Last digit of the year.	6
	I	Last digit of the ISO year.	6
	YEAR	Name of the year in uppercase.	TWO THOUSAND-SIX
	Year	Name of the year with the first letter in uppercase.	Two Thousand-Six
Month	MM	Two-digit month of the year.	01
	MONTH	Full name of the month in uppercase, right- padded with spaces to a total length of nine characters .	JANUARY
	Month	Full name of the month with first letter in uppercase, right-padded with spaces to a total length of nine characters.	January
	MON	First three letters of the name of the month in uppercase.	JAN
	Mon	First three letters of the name of the month with the first letter in uppercase.	Jan
	RM	Roman numeral month.	The Roman numeral month for the fourth month (April) is IV .
Week	WW	Two-digit week of the year.	02
	IW	Two-digit ISO week of the year.	02
	W	One-digit week of the month.	2
Day	DDD	Three-digit day of the year.	103
	DD	Two-digit day of the month.	31
	D	One-digit day of the week.	5
	DAY	Full name of the day in uppercase.	SATURDAY
	Day	Full name of the day with the first letter in uppercase.	Saturday
	DY	First three letters of the name of the day in uppercase.	SAT
	Dy	First three letters of the name of the day with the first letter in uppercase.	Sat
	J	Julian day ”the number of days that have passed since January 1, 4713 B.C.	2439892
Hour	HH24	Two-digit hour in 24-hour format.	23
	HH	Two-digit hour in 12-hour format.	11
Minute	MI	Two-digit minute.	57
Second	SS	Two-digit second.	45
	FF[1..9]	Fractional seconds with an optional number of digits to the right of the decimal point. Only applies timestamps, which you'll learn about later in the section Using Timestamps.	When dealing with 0.123456789 seconds, FF3 would round to 0.123.
	SSSSS	Number of seconds past 12 a.m.	46748
	MS	Millisecond (millionths of a second).	100
	CS	Centisecond (hundredths of a second).	10
Separators	-/,.;: text	Characters that allow you to separate the aspects of a date and time. You can supply freeform text in quotes as a separator.	When dealing with the date December 13, 1969, DD-MM-YYYY would produce 12-13-1969 and DD/MM/YYYY would produce 12/13/1969
Suffixes	AM or PM	AM or PM as appropriate.	AM
	A.M. or P.M.	A.M. or P.M. as appropriate.	P.M.
	AD or BC	AD or BC as appropriate.	AD
	A.D. or B.C.	A.D. or B.C. as appropriate.	B.C.
	TH	Suffix to a number. You can make the suffix uppercase by specifying the numeric format in uppercase and vice versa for lowercase.	When dealing with a day number of 28, ddTH would produce 28th and DDTH would produce 28TH
	SP	Number is spelled out.	When dealing with a day number of 28, DDSP would produce TWENTY-EIGHT and ddSP would produce twenty-eight
	SPTH	Combination of TH and SP.	When dealing with a day number of 28, DDSPTH would produce TWENTY- EIGHTH and ddSPTH would produce twenty-eighth
Era	EE	Full era name for Japanese Imperial, ROC Official, and Thai Buddha calendars.	No example
	E	Abbreviated era name.	No example
Time zones	TZH	Time zone hour. You'll learn about time zones later in the section Understanding Time Zones.	12
	TZM	Time zone minute.	30
	TZR	Time zone region.	PST
	TZD	Time zone with daylight savings information.	No example

The following table shows examples of strings to format the date February 5, 1968, along with the string returned from a call to TO_CHAR().

Format String	Returned String
MONTH DD, YYYY	FEBRUARY 05, 1968
MM/DD/YYYY	02/05/1968
MM-DD-YYYY	02-05-1968
DD/MM/YYYY	05/02/1968
DAY MON, YY AD	MONDAY FEB, 68 AD
DDSPTH of MONTH, YEAR A.D.	FIFTH of FEBRUARY, NINETEEN SIXTY-EIGHT A.D.
CC, SCC	20, 20
Q	1
YYYY, IYYY, RRRR, SYYYY, Y,YYY, YYY, IYY, YY, IY, RR, Y, I, YEAR, Year	1968, 1968, 1968, 1968, 1,968, 968, 968, 68, 68, 68, 8, 8, NINETEEN SIXTY-EIGHT, Nineteen Sixty-Eight
MM, MONTH, Month, MON, Mon, RM	02, FEBRUARY , February , FEB, Feb, II
WW, IW, W	06, 06, 1
DDD, DD, DAY, Day, DY, Dy, J	036, 05, MONDAY , Monday , MON, Mon, 2439892
ddTH, DDTH, ddSP, DDSP, DDSPTH	05th, 05TH, five, FIVE, FIFTH

You can see the results shown in this table by calling TO_CHAR() in a query. The following query converts February 5, 1968, to a string with the format MONTH DD, YYYY :

  SELECT TO_CHAR(TO_DATE('05-FEB-1968'), 'MONTH DD, YYYY')   FROM dual;  TO_CHAR(TO_DATE('0 ------------------ FEBRUARY  05, 1968 

Note  

The TO_DATE() function converts a string to a datetime. You ll learn more about the TO_DATE() function shortly.

The following table shows examples of strings to format the time 19:32:36 (32 minutes and 36 seconds past 7 P.M.) ”along with the output that would be returned from a call to TO_CHAR() with that time and format string.

Format String	Returned String
HH24:MI:SS	19:32:36
HH.MI.SS AM	7.32.36 PM

Using TO_DATE() to Convert a String to a Datetime

You can use TO_DATE(x[, format]) to convert the x string to a datetime. You can provide an optional format string to indicate the format of x. If you omit format , your date can be in the default format DD-MON-YYYY or DD-MON-YY.

Note  

The NLS_DATE_FORMAT database parameter specifies the default date format. As you ll learn later in the section Setting the Default Date Format, you can change the setting of NLS_DATE_FORMAT.

The following example uses TO_DATE() to convert the strings 04-JUL-2006 and 04-JUL-06 to the date July 4, 2006; notice the final date is displayed in the default format of DD-MON-YY:

  SELECT TO_DATE('04-JUL-2006'), TO_DATE('04-JUL-06')   FROM dual;  TO_DATE(' TO_DATE(' --------- --------- 04-JUL-06 04-JUL-06 

Specifying a Datetime Format

As mentioned, you can supply an optional format for your datetime to TO_DATE(). You use the same format parameters as those defined previously in Table 4-2. The following example uses TO_DATE() to convert the string July 4, 2006 to a date, passing the format string MONTH DD, YYYY to TO_DATE():

  SELECT TO_DATE('July 4, 2006', 'MONTH DD, YYYY')   FROM dual;  TO_DATE(' --------- 04-JUL-06 

The next example passes the format string MM.DD.YY to TO_DATE() and converts the string 7.4.06 to the date July 4, 2006; again, the final date is displayed in the default format DD-MON-YY:

  SELECT TO_DATE('7.4.06', 'MM.DD.YY')   FROM dual;  TO_DATE(' --------- 04-JUL-06 

Specifying Times

You can, of course, specify a time with a datetime. If you don t supply a time with a datetime, the time part of your datetime defaults to 12:00:00 A.M. You can supply the format for a time using the various formats shown earlier in Table 4-3. One example time format is HH24:MI:SS, where:

• HH24 is a two-digit hour in 24-hour format from 00 to 23.

• MI is a two-digit minute from 00 to 59.

• SS is a two-digit second from 00 to 59.

An example of a time that uses the HH24:MI:SS format is 19:32:36. A full example datetime that uses this time is

 05-FEB-1968 19:32:36 

with the format for this datetime being

 DD-MON-YYYY HH24:MI:SS 

The following TO_DATE() call shows the use of this datetime format and value:

 TO_DATE('05-FEB-1968 19:32:36', 'DD-MON-YYYY HH24:MI:SS') 

The datetime returned by TO_DATE() in the previous example is used in the following INSERT statement that adds a row to the customers table. Notice the dob column for the new row is set to the datetime returned by TO_DATE():

 INSERT INTO customers (   customer_id, first_name, last_name,   dob,   phone ) VALUES (   6, 'Fred', 'Brown',   TO_DATE('05-FEB-1968 19:32:36', 'DD-MON-YYYY HH24:MI:SS'),   '800-555-1215'   ); 

You use TO_CHAR() to view the time part of a datetime. For example, the following query retrieves the rows from the customers table and uses TO_CHAR() to convert the dob column values; notice customer #6 has the time previously set in the INSERT :

  SELECT customer_id, TO_CHAR(dob, 'DD-MON-YYYY HH24:MI:SS')   FROM customers;  CUSTOMER_ID TO_CHAR(DOB,'DD-MON- ----------- --------------------           1 01-JAN-1965 00:00:00           2 05-FEB-1968 00:00:00           3 16-MAR-1971 00:00:00           4           5 20-MAY-1970 00:00:00           6 05-FEB-1968 19:32:36  ROLLBACK;  

Notice the time for the dob column for customers #1, #2, #3, and #5 is set to 00:00:00 (12 A.M.). This is the default time substituted by the database when you don t provide a time in a datetime.

Note  

If you actually ran the earlier INSERT statement in SQL*Plus, make sure you undo the change using ROLLBACK .

Combining TO_CHAR() and TO_DATE() Calls

You can combine TO_CHAR() and TO_DATE() calls. This allows you to supply and view datetimes in different formats. For example, the following query combines TO_CHAR() and TO_DATE() to view just the time part of a datetime; notice that the output from TO_DATE() is passed to TO_CHAR():

  SELECT TO_CHAR(TO_DATE('05-FEB-1968 19:32:36',   'DD-MON-YYYY HH24:MI:SS'), 'HH24:MI:SS')   FROM dual;  TO_CHAR( -------- 19:32:36