6-4. Converting a Date to a String
Problem
You want to convert a date into a nicely formatted string value. For example, you are converting a legacy application from another database vendor into a web-based Oracle application. A few of the fields on the web form are dates. The users of the application expect to see the dates in a specific format, so you need the dates to be formatted in a particular manner for display.
Solution
Use the TO_CHAR function using the date masks. The TO_CHAR function offers many formatting options for returning a string from a DATE value. The following function accepts an EMPLOYEE_ID value and returns a representation of the HIRE_DATE spelled out.
CREATE OR REPLACE PROCEDURE obtain_emp_hire_date(emp_id IN NUMBER)
AS
emp_hire_date employees.hire_date%TYPE;
emp_first employees.first_name%TYPE;
emp_last employees.last_name%TYPE;
BEGIN
SELECT hire_date, first_name, last_name
INTO emp_hire_date, emp_first, emp_last
FROM employees
WHERE employee_id = emp_id;
DBMS_OUTPUT.PUT_LINE(emp_first || ' ' || emp_last ||
' was hired on: ' ||
TO_CHAR(emp_hire_date, 'DAY MONTH DDTH YYYY'));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found for the given ID'),
END;
If you pass the employee ID of 200 to this function, then it will return a result in the following format:
How It Works
As shown in the previous recipe, the TO_CHAR function accepts a NUMBER or DATE value and returns a nicely formatted string. Using the many formatting masks that are available, you can return a string-based representation in a number of ways. As demonstrated in the solutions to this recipe and the previous one, the TO_CHAR function works a bit differently than the other conversion functions because the formatting mask is used to help produce the final string. Other conversion functions use the formatting mask to represent the format of the string you are passing in. In other words, TO_CHAR produces the formatted strings, whereas the other conversion functions accept them and produce a different datatype.
Table 6-4 lists some of the most commonly used characters for converting dates into strings.
Table 6-4. Date Formatting Mask Characters
There are several formatting options, as you can see. It is best to spend some time with each of the different combinations to decide upon which one works best for your solution.
PL/SQL can make date formatting easy, because it is possible to create your own function that returns a date formatted per your application's requirements. Sometimes it is difficult to remember all the different formatting options that are available for dates. It can also be quite painful to reference a table such as Table 6-4 each time you want to format a date string. You can instead create your own conversion function to support just the formats that you use, and no others. Such a function greatly reduces the possibility for error, thus enhancing consistency in how your application formats dates.
The function in the following example accepts two parameters: the date to be converted and a string that specifies the output format. The second argument is limited to only four, easy-to-remember values: LONG, SHORT, STD, and DASH.
-- Returns a date string formatted per the style
-- that is passed into it. The possible style strings
-- are as follows:
-- LONG => The spelled out date
-- SHORT => The abbreviated date
-- STD or blank => The standard date format mm/dd/yyyy
-- DASH => The standard format with dashes mm-dd-yyyy
CREATE OR REPLACE FUNCTION FORMAT_DATE(in_date IN DATE,
style IN VARCHAR2)
RETURN VARCHAR2 AS
formatted_date VARCHAR2(100);
BEGIN
CASE style
WHEN 'LONG' THEN
formatted_date := TO_CHAR(in_date, 'DAY MONTH DDTH YYYY'),
WHEN 'SHORT' THEN
formatted_date := TO_CHAR(in_date, 'DY MON DDTH YYYY'),
WHEN 'DASH' THEN
formatted_date := TO_CHAR(in_date, 'MM-DD-YYYY'),
ELSE
formatted_date := TO_CHAR(in_date, 'MM/DD/YYYY'),
END CASE;
RETURN formatted_date;
END;
This function is nice because you only need to remember a short string that is used to represent the date format that you'd like to return.
It is also possible to convert dates to strings using the CAST function. For more information on the use of the CAST function, please see Recipe 6-5.